如何在SQL Server中执行SELECT *以同时获得表名和字段的别名

Question

是否可以使用SELECT *或类似的简单方法在别名中获得表名?

不同表中相同的字段名

CREATE TABLE table1 (field1 INT, field2 INT)
CREATE TABLE table2 (field1 INT, field2 INT)
CREATE TABLE table3 (field1 INT, field2 INT)

如果我运行SELECT *命令

SELECT *
FROM table1, table2, table3

我得到这样的结果(示例数据只显示第一行):

field1 field2 field1 field2 field1 field2
-----------------------------------------
1      1      1      1      2      1   

但是我想得到这个:

table1.field1 table1.field2 table2.field1 table2.field2 table3.field1 table3.field2
-----------------------------------------------------------------------------------
1             1             1             1             2             1             

在这个例子中，我可以更好地识别不同的值，使用别名table3.field1

注意:我不需要把SELECT * FROM改成SELECT table1.*, table2.*, table3.* FROM。在实际情况中，我有近20-30个不同的表，每个表有近10个字段。所以它对我没有用处。

Answer 1

我认为这只能使用基于information_schema构建列列表的动态SQL来完成。比如:

CREATE TABLE table1 (field1 INT, field2 INT)
CREATE TABLE table2 (field1 INT, field2 INT)
CREATE TABLE table3 (field1 INT, field2 INT)
DECLARE @tables TABLE (seq INTEGER IDENTITY(1,1), name SYSNAME)
INSERT @tables VALUES ('table1'), ('table2'), ('table3')
DECLARE @sql NVARCHAR(MAX) =
'SELECT '
+ STUFF((
SELECT ', [' + C.TABLE_NAME + '.' + C.COLUMN_NAME + '] = [' + C.TABLE_NAME + '].[' + C.COLUMN_NAME + ']'
FROM @tables T
JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_NAME = T.name AND C.TABLE_SCHEMA = 'dbo'
ORDER BY T.seq, C.ORDINAL_POSITION
FOR XML PATH('')
), 1, 2, '')
+ CHAR(10) + 'FROM '
+ STUFF((
SELECT ', [' + T.name + ']'
FROM @tables T
ORDER BY T.seq
FOR XML PATH('')
), 1, 2, '')
INSERT table1 VALUES (1, 2), (3, 4)
INSERT table2 VALUES (5, 6)
INSERT table3 VALUES (7, 8), (9, 10)
PRINT @sql
EXEC (@sql)
DROP TABLE table1
DROP TABLE table2
DROP TABLE table3

生成SQL

SELECT [table1.field1] = [table1].[field1], [table1.field2] = [table1].[field2], [table2.field1] = [table2].[field1], [table2.field2] = [table2].[field2], [table3.field1] = [table3].[field1], [table3.field2] = [table3].[field2]
FROM [table1], [table2], [table3]

输出

tbody> <<tr>1

table1.field1	table2.field1	table2.field2	table3.field1	table3.field2	table3.field1	table3.field2	table3.field2
1	2	5	6	7	8
	2	5	6	9	10
3	4	5	6	7	8
3	4	5	6	9	10