Is it possible to concatenate query results to form a new query?

Question

I have now:

SELECT TABLE_SCHEMA ,
 TABLE_NAME ,
 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NNN';

This gives me several hundreds of rows, because several tables contain field with that name. I want to search some value from these tables and columns by using the previous data in new query like:

SELECT <COLUMN_NAME> FROM <TABLE_SCHEMA>.<TABLE_NAME>

Could I include the results of the first query to the tags in second one in one query and how? I would like to avoid calling the every schema, table and column by hand to check only minor things in every table.

Source:

I took the first query from: https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database

Answer 1

Unless you are explicitly trying to write queries that work in multiple engines, I recommend sticking to the catalog views in the sys schema rather than INFORMATION_SCHEMA (see this and this). You'll need dynamic SQL for this anyway, and in other engines the syntax for this will vary greatly:

DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'SELECT ' + QUOTENAME(c.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
WHERE c.name = N'NNN';
EXEC sys.sp_executesql @sql;

If you want the column to be listed as the name of the table instead of the name of the column:

...
SELECT @sql += N'SELECT ' + QUOTENAME(t.name) + N' = '
 + QUOTENAME(s.name + N'.' + t.name) + N' FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...

And if you want them all in a single result set, you could add the table name as a column, and use a UNION (and coerce all the columns into a compatible type, just in case):

...
SELECT @sql += N'UNION ALL SELECT [table] = '''
 + QUOTENAME(s.name + N'.' + t.name) + N''', '
 + N' value = CONVERT(nvarchar(4000),'
 + QUOTENAME(c.name) + N') FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM 
...
SET @sql = STUFF(@sql, 1, 10, N'');
...