Sql-server – Is it possible to concatenate query results to form a new query

information-schemasql serversql-server-2012

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

Best Answer

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'');
...

Related Solutions

Sql-server – How to create implicit select statements

Not sure that this is simpler than a cursor solution, but here is what I would do:

Before we begin we need a few tables in a SQL Fiddle:

MS SQL Server 2012 Schema Setup

CREATE TABLE dbo.tbl1(c1 INT, c2 INT, c3 INT);
CREATE TABLE dbo.tbl2(c4 INT, c5 INT, c6 INT);
CREATE TABLE dbo.tbl3(c7 INT, c8 INT, c9 INT);

First we need the list of all user tables. For that we can use the sys.tables catalog view. The OBJECT_SCHEMA_NAME() function gets us the schema name, the OBJECT_NAME() function the table name. The QUOTENAME() function quotes the names correctly, in case some of those contain special characters, key words or spaces. (In this example the use of OBJECT_NAME() is not strictly necessary as sys.tables has a name column, but I left it in as you can use this pattern with any catalog view that has an object_id column.)

Query 1:

SELECT 
  QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id))+'.'+
  QUOTENAME(OBJECT_NAME(T.object_id)) AS quoted_table_name,
  T.object_id
FROM sys.tables AS T;

Results:

| QUOTED_TABLE_NAME | OBJECT_ID |
|-------------------|-----------|
|      [dbo].[tbl1] | 245575913 |
|      [dbo].[tbl2] | 261575970 |
|      [dbo].[tbl3] | 277576027 |

The next step is to get the list of column names, again quoted. We can use the sys.columns catalog view for that.

Query 2:

SELECT C.name,C.column_id 
  FROM sys.columns AS C
 WHERE C.object_id = OBJECT_ID('dbo.tbl1');

Results:

| NAME | COLUMN_ID |
|------|-----------|
|   c1 |         1 |
|   c2 |         2 |
|   c3 |         3 |

The next hurdle is to get those columns in a comma separated list. There is no string concatenation aggregate function build in so we have to use a trick:

Query 3:

SELECT STUFF((
  SELECT ','+QUOTENAME(name)
    FROM sys.columns AS C
   WHERE C.object_id = OBJECT_ID('dbo.tbl1')
   ORDER BY C.column_id
     FOR XML PATH(''),TYPE
  ).value('.','NVARCHAR(MAX)'),1,1,'') AS clomun_list;

Results:

|    CLOMUN_LIST |
|----------------|
| [c1],[c2],[c3] |

With that all pieces are in place and we just have to put them all together:

Query 4:

SELECT 'SELECT ' +
       CL.column_list +
       ' FROM ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id)) + '.' +
       QUOTENAME(OBJECT_NAME(T.object_id)) +
       ';' AS select_statement
FROM sys.tables AS T
CROSS APPLY (
  SELECT STUFF((
    SELECT ','+QUOTENAME(name)
      FROM sys.columns AS C
     WHERE C.object_id = T.object_id
     ORDER BY C.column_id
       FOR XML PATH(''),TYPE
    ).value('.','NVARCHAR(MAX)'),1,1,'') AS column_list
  )CL;

Results:

|                         SELECT_STATEMENT |
|------------------------------------------|
| SELECT [c1],[c2],[c3] FROM [dbo].[tbl1]; |
| SELECT [c4],[c5],[c6] FROM [dbo].[tbl2]; |
| SELECT [c7],[c8],[c9] FROM [dbo].[tbl3]; |

This example will work in SQL 2005 and later, assuming you are on the latest service pack. There is no clean solution to achieve this with SQL Server 2000. In that case you need to go back to your cursor solution.

Postgresql – Use result from table_schema query as in select * from

You can't do this with ordinary SQL, at least in PostgreSQL. You need to use PL/PgSQL to generate dynamic SQL and run it with EXECUTE. The

RETURN QUERY EXECUTE format('SELECT ... FROM %I WHERE $1, $2', tablename) USING param1, param2;

construct is useful.

See executing dynamic statements.

Best Answer

Related Solutions

Sql-server – How to create implicit select statements

Related Question