Can't rename columns with dynamic query

Question

Renaming columns in a query is a very basic and common task for any sql admin/developer. But today I faced this problem where I needed to select the table name of several hundreds of tables and match it with the latest entry in each table that was selected previously.

In the end I wanted something like this:

event_id | date       | tbl_name
==================================
68       | 31-12-1998 | tb_x
56       | 31-11-2001 | tb_y

To get the event_id and date were easy enough with a dynamic query but when I try to add the column tbl_name it fails.

The procedure is simple:

SELECT @sql = @sql + 'SELECT event_id, date, '+QUOTENAME(TABLE_NAME)+' as tbl_name FROM ' + TABLE_NAME + ' UNION ' 
FROM (SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME not like 'event_%') AS t
...
exec (@sql)

In the end the query should look something like this:

SELECT event_id, date, 'event_b559453b' as tbl_name
FROM event_b559453b
UNION
...
SELECT event_id, date, 'event_b03c4520' as tbl_name
FROM event_b559453b

The strange thing (to me) is that if you run that last query it will do as expected but if you use exec @sql it will fail with an error saying Invalid column name 'event_????'. Could anyone tell me why the column renaming isn't happening, and if there's a way around this?

Thanks in advance.

NOTE: I'm using SQL Server 2012 and I'm relatively new to this!

Answer 1

Get rid of the double quote. And try using coalesce like this:

DECLARE @sql VARCHAR(MAX);

SELECT @sql = COALESCE(@sql + ' UNION ','') + 'SELECT event_id, date, ''' + TABLE_NAME + ''' as tbl_name FROM ' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME not like 'event_%'

SELECT @sql --look at what your executing
--EXEC(@sql)