Given a table "ABC" with columns Col1, Col2 and Col3 it is possible to automatically generate something like the following:
给定具有列Col1,Col2和Col3的表“ABC”,可以自动生成如下内容:
SELECT
Col1 AS 'ABC_Col1',
Col2 AS 'ABC_Col2',
Col3 AS 'ABC_Col3'
FROM ABC
I have a table without a 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 columns with the names Col1, Col2 etc...)
我有一个没有固定列的表(用户可以附加自己的列),我仍然需要列前缀(因为在JOIN / CTE中需要它与其他表也有名称为Col1,Col2的列等等...)
Therefore I would like to be able to write something like this:
因此,我希望能够写出这样的东西:
SELECT
T0.* AS 'ABC_T.*',
FROM ABC T0
Which is of course not valid SQL, but can it be done somehow so the "*" columns all get the same prefix?
这当然不是有效的SQL,但它可以以某种方式完成,所以“*”列都获得相同的前缀?
3 个解决方案
#1
3
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.
从那里它只是一些动态的SQL。我还在玩它。
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)
A)这是糟糕的表现(因为它是一个光标)
B) I know you're not meant to do work for people on here, but I got carried away.
B)我知道你不打算为这里的人做工,但我被带走了。
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.
C)我认为甚至没有发布这个因为我觉得它的答案有多差,但这至少不是一个想法。
#2
2
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]
.
您似乎对列别名的作用感到困惑。正如您在select子句中看到的那样,您只能通过引用T0来选择T0中的字段。*。您仍然可以在查询中稍后将这些字段引用为T0。
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.
编辑:更清楚的是,您不能通过别名来更改字段的前缀。您只能更改它的名称。字段的前缀是它来自的表的别名。
#3
0
I think the only way you'll be able to do this is by creating some dynamic SQL.
我认为你能够做到这一点的唯一方法是创建一些动态SQL。
#1
3
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.
从那里它只是一些动态的SQL。我还在玩它。
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)
A)这是糟糕的表现(因为它是一个光标)
B) I know you're not meant to do work for people on here, but I got carried away.
B)我知道你不打算为这里的人做工,但我被带走了。
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.
C)我认为甚至没有发布这个因为我觉得它的答案有多差,但这至少不是一个想法。
#2
2
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]
.
您似乎对列别名的作用感到困惑。正如您在select子句中看到的那样,您只能通过引用T0来选择T0中的字段。*。您仍然可以在查询中稍后将这些字段引用为T0。
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.
编辑:更清楚的是,您不能通过别名来更改字段的前缀。您只能更改它的名称。字段的前缀是它来自的表的别名。
#3
0
I think the only way you'll be able to do this is by creating some dynamic SQL.
我认为你能够做到这一点的唯一方法是创建一些动态SQL。