Sql Select Join: How to Prefix All Columns as 'Prefix.*'

SQL select join: is it possible to prefix all columns as 'prefix.*'?

I see two possible situations here. First, you want to know if there is a SQL standard for this, that you can use in general regardless of the database. No, there is not. Second, you want to know with regard to a specific dbms product. Then you need to identify it. But I imagine the most likely answer is that you'll get back something like "a.id, b.id" since that's how you'd need to identify the columns in your SQL expression. And the easiest way to find out what the default is, is just to submit such a query and see what you get back. If you want to specify what prefix comes before the dot, you can use "SELECT * FROM a AS my_alias", for instance.

In a join, how to prefix all column names with the table it came from

You could

select ah.*, l.*, u.*, pi.* from ...

then the columns will be returned ordered by table at least.

For better distinction between every two sets of columns, you could also add "delimiter" columns like this:

select ah.*, ':', l.*, ':', u.*, ':', pi.* from ...

^{(Edited to remove explicit aliases as unnecessary, see comments.)}

SQL: How do add a prefix to column names in a JOIN?

Used aliasing to retrieve all values associated from all three tables. if you want to reference only specific column do so by using the alias_name.column_name instead of p.*, where * means all columns belonging to table that the alias is associated with( ie. p refers to phlegm).

SELECT p.*, m.*, s.* 
FROM phlegm p 
JOIN mucus m ON p.id = m.id 
JOIN snot s ON p.id = s.id;

I removed the WHERE from your original query above, not sure why it was there.

SQL JOIN : Prefix fields with table name

Try this way

    SELECT groups.id as group_id,  groups.name as group_name ,
          t.id as cons_id,  t.name as cons_name,  t.type as cons_type,
          a.groupid ,  a.constraintid
   FROM constraints_to_group as a
    JOIN groups on groups.id=a.groupid
    JOIN constraints as t on t.id=a.constraintid

Prefix all columns in T-SQL statement

This will give you a map of old column names and new column names:

SELECT syscolumns.name as old_column_name, 'ABC_' + syscolumns.name as new_column_name
   FROM sysobjects 
        JOIN syscolumns ON sysobjects.id = syscolumns.id
   WHERE sysobjects.name = 'ABC'
ORDER BY sysobjects.name,syscolumns.colid

From there it's just some dynamic sql. I'm still playing with it.

EDIT

OK, I ditched that.

DECLARE @sql varchar(max)
SET @sql = 'SELECT '

DECLARE @old_column_name varchar(50)
DECLARE @getNext CURSOR
SET @getNext = CURSOR FOR 
    SELECT syscolumns.name
       FROM sysobjects 
            JOIN syscolumns ON sysobjects.id = syscolumns.id
       WHERE sysobjects.name = 'ABC'
OPEN @getNext
FETCH NEXT FROM @getNext INTO @old_column_name
WHILE @@fetch_status = 0
BEGIN

    --BUILD DYNAMIC SQL
    SET @sql = @sql + @old_column_name + ' AS ''ABC_' + @old_column_name + ''', '

FETCH NEXT FROM @getNext INTO @old_column_name
END
CLOSE @getNext
DEALLOCATE @getNext

--REMOVE FINAL COMMA AND ADD TABLE
SET @sql = SUBSTRING(@sql, 0, LEN(@sql)) + ' FROM ABC'

exec(@sql)

A) this is terrible performance (because it's a cursor)

B) I know you're not meant to do work for people on here, but I got carried away.

C) I considered not even posting this because of how poor of an answer I feel it is, but it's a least an idea.

Add a prefix for each column

You can try something like this.

    DECLARE @Colums AS TABLE(IndexNo INT IDENTITY(1,1), OldCol VARCHAR(MAX), NewCol VARCHAR(MAX))
    INSERT INTO @Colums
    SELECT syscolumns.name as old_column_name, 'ABC_' + syscolumns.name as new_column_name
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    WHERE sysobjects.name = 'ABCtable'
    ORDER BY sysobjects.name,syscolumns.colid


    DECLARE @I INT=1;
    WHILE EXISTS(SELECT * FROM @Colums WHERE IndexNo=@I)
    BEGIN
        DECLARE @OldCol VARCHAR(MAX)='', @NewCol VARCHAR(MAX)=''
        SELECT @OldCol='ABCtable.'+OldCol, @NewCol=NewCol FROM @Colums WHERE IndexNo = @I

        EXEC sp_rename @OldCol, @NewCol, 'COLUMN'

        SET @I = @I+1;
    END

---

Related Topics