1

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

0

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.