views:

167

answers:

3

Hi

Given a table "ABC" with columns Col1, Col2 and Col3 it is possible to write the following

SELECT
Col1 AS 'ABC_Col1', 
Col2 AS 'ABC_Col2',
Col3 AS 'ABC_Col3' 
FROM ABC

This is ok, but i have a table with a not fixed set of columns (users are able to append their own columns) where I still need the column prefix (because it is needed in a JOIN/CTE with other tables that also have a Col1, Col2 etc...)

Therefor I would like to be able to write something like this:

SELECT
T0.* AS 'ABC_T.*', 
FROM ABC T0

Which is of cause not valid SQL, but can it be done somehow so the "*" columns all get the same prefix?

/Rasmus

+2  A: 

You seem confused as to what column aliases do. As you can see in your select clause, you're already only selecting fields from T0 by referencing T0.*. You can still reference those fields as T0.<whatever> later in your query without aliasing the fields, you will just have to refer to them by their full field name, ie, T0.[My Users Suck And Make Really Long Field Names].

EDIT: To be more clear, you can not change the prefix of a field by aliasing it. You can only change the name of it. The prefix of the field is the alias of the table that it comes from.

Donnie
Downvoter care to comment? Nothing I said here is incorrect.
Donnie
@Donnie: Likely someone's tactical downvoting
OMG Ponies
I know T0.* can be referenced as To.<Whatever> but as I mention this sample is later to be used in a more advance join and a Common Table Expression (CTE). And at that time there will be a T0, T1, T2, etc where some of the fields have the same column names.. in joins * will still work but in CTE's it is not possible to have the same column-names twice...
RWJ
A: 

I think the only way you'll be able to do this is by creating some dynamic SQL.

Jim
+1  A: 

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.

Nick Spiers
Thank you for the effort.. I was hoping there was another way than dynamic sql (and since there seem not to be I'm not going to go on this route)...
RWJ