How to get data from multiple tables in one row I am looking for a way to get the table name and description field from all tables in my database that start with T_. What I have up till now is this:

DECLARE @tables TABLE(table_name VARCHAR(100), column_name VARCHAR(100), row INT); 

INSERT INTO @tables (table_name, column_name, row)
   SELECT
       tables.name AS table_name,
       columns.name AS column_name,
       ROW_NUMBER() OVER (ORDER BY tables.name)
   FROM 
       sys.tables
   INNER JOIN 
       sys.columns ON tables.object_id =  columns.object_id
   WHERE 
       tables.name LIKE 'T_%' AND columns.name = 'DESCRIPTION'

DECLARE @i INT
DECLARE @count INT

SELECT @i = MIN(row) -1, @count = MAX(row) 
FROM @tables

WHILE @i &lt; @count
BEGIN
    SELECT @i = @i + 1
    DECLARE @q VARCHAR(400)
    SELECT @q = 'SELECT  ''' + (SELECT table_name FROM @tables WHERE row = @i) + ''' AS TABLE_NAME, '+ (SELECT column_name FROM @tables WHERE row = @i) + ' FROM ' + (SELECT table_name FROM @tables WHERE row = @i)
    EXEC(@q)
END


This results in the data I need, however in multiple tables. How can I get that data in one table?
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);