为 T-SQL 语句中的所有列添加前缀 [英] Prefix all columns in T-SQL statement
问题描述
给定一个包含 Col1、Col2 和 Col3 列的ABC"表,可以自动生成如下内容:
Given a table "ABC" with columns Col1, Col2 and Col3 it is possible to automatically generate something like the following:
SELECT
Col1 AS 'ABC_Col1',
Col2 AS 'ABC_Col2',
Col3 AS 'ABC_Col3'
FROM ABC
我有一个没有固定列集的表(用户可以附加他们自己的列),我仍然需要列前缀(因为在 JOIN/CTE 中需要它,其他表也有带有名称的列Col1, Col2 等...)
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...)
因此我希望能够写出这样的东西:
Therefore I would like to be able to write something like this:
SELECT
T0.* AS 'ABC_T.*',
FROM ABC T0
这当然不是有效的 SQL,但可以以某种方式完成,以便*"列都获得相同的前缀?
Which is of course not valid SQL, but can it be done somehow so the "*" columns all get the same prefix?
推荐答案
这将为您提供旧列名称和新列名称的映射:
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
从那里开始,它只是一些动态 sql.我还在玩它.
From there it's just some dynamic sql. I'm still playing with it.
编辑
好的,我放弃了.
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) 这是糟糕的表现(因为它是一个游标)
A) this is terrible performance (because it's a cursor)
B) 我知道你不打算为这里的人工作,但我被冲昏了头脑.
B) I know you're not meant to do work for people on here, but I got carried away.
C) 我什至考虑过不发布这个,因为我觉得它的答案很糟糕,但这至少是一个想法.
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.
这篇关于为 T-SQL 语句中的所有列添加前缀的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!