How to get data from multiple tables in one row
Build the SELECT statements
Add UNION ALL for all except the first row at the start

Easier than adding to the endexcept for the last row...

Use the XML PATH technique to concat rows into one string


Tested code:

DECLARE @sql nvarchar(max), @base nvarchar(max) = '';

SET @SQL = 
    (
    SELECT 
        CASE WHEN X.rn &gt;= 2 THEN ' UNION ALL ' ELSE '' END + X.stmt
    FROM
        (
        SELECT
            @base + 'SELECT ''' + t.name + ''' AS table_name, ' + c.name + ' FROM ' + t.name AS stmt,
            ROW_NUMBER() OVER (ORDER BY t.object_id) AS rn
        FROM 
            sys.tables t
            INNER JOIN
            sys.columns c ON t.object_id = c.object_id
        WHERE 
            t.name LIKE 'T%' AND c.name = 'DESCRIPTION'
        ) X
    ORDER BY X.rn
    FOR XML PATH ('')
    );
PRINT @sql;
EXEC (@sql);


SQL Server 2012+ version:

DECLARE @sql nvarchar(max), @base nvarchar(max) = '';

SET @SQL = 
    (
    SELECT 
        CONCAT(CASE WHEN X.rn &gt;= 2 THEN ' UNION ALL ' ELSE '' END, X.stmt)
    FROM
        (
        SELECT
            CONCAT(@base, 'SELECT ''', t.name , ''' AS table_name, ', c.name, ' FROM ', t.name) AS stmt,
            ROW_NUMBER() OVER (ORDER BY t.object_id) AS rn
        FROM 
            sys.tables t
            INNER JOIN
            sys.columns c ON t.object_id = c.object_id
        WHERE 
            t.name LIKE 'T%' AND c.name = 'DESCRIPTION'
        ) X
    ORDER BY X.rn
    FOR XML PATH ('')
    );
PRINT @sql;
EXEC (@sql);