CODEWITHSUNDEEP
sqlsql-serverselectalias
Jota Pardo
Jota Pardo

Reputation: 878

How to perform SELECT * in SQL Server to get alias with table name and field at the same time

Is it possible to get the table name in the alias in addition with the field name using some easy way of SELECT * or similar?

Same name of fields in different tables.

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

If I run a SELECT * command

SELECT *
FROM table1, table2, table3

I get this kind of result (example data just showing first row):

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

but I want to get this:

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

In this example, I can identify better the different value faster with the alias table3.field1

NOTE: I don't need to change the SELECT * FROM to SELECT table1.*, table2.*, table3.* FROM. In the real case I have almost 20-30 different tables, with almost 10 fields each one of them. So it is not useful for me.

Upvotes: 1

Views: 322

Answers (1)

T N
T N

Reputation: 10023

I believe this could only be done using dynamic SQL that builds up the column list based on information_schema. Something like:

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

Generated 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]

Output

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

The FOR XML PATH('') construct is a convenient (if not obfuscated) way of concatenating results. The STUFF() is used to strip the first comma/space. If you use schemas other than dbo, the above would need some enhancements to take that into account. I expect more intelligent JOIN logic would also be needed.

Upvotes: 1

Related Questions