query columns from many tables using dynamic SQL or Correlated Subquery on SQL Server INFORMATION_SCHEMA

Question

I want to list all the Manufacturers identified in an SQL Server database.

There are many tables with manufacturer names in them, but they are not always in a nicely named column. However, column names are LIKE %manuf%.

So:

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%manuf%' 
ORDER BY TABLE_NAME;

gets me a list of tables I want to query from and the column name I want to query.

How can I now query the manufacturer names from those tables?

Answer 1

If you want all of them returned in one result set you can use this query:

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = STUFF((
SELECT ' UNION ALL SELECT '+QUOTENAME(COLUMN_NAME)+' FROM '+QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%manuf%' 
ORDER BY TABLE_NAME
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,11,'')
;

EXEC( @cmd);

That requires all of them to be a (n)varchar data type. If you run into problems add a cast:

SELECT ' UNION ALL SELECT CAST('+QUOTENAME(COLUMN_NAME)+' AS NVARCHAR(MAX)) FROM '+QUOTENAME(TABLE_NAME)

This seems like a very odd database design. You might want to revisit that...

---

If INFORMATION_SCHEMA isn't working for you use this:

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = STUFF((
SELECT ' UNION ALL SELECT '+QUOTENAME(c.name)+' FROM dbo.'+QUOTENAME(t.name)
FROM sys.columns c
JOIN sys.tables t
ON c.object_id = t.object_id
WHERE c.name LIKE '%name%' 
AND t.is_ms_shipped = 0
ORDER BY t.name
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,11,'')
;

EXEC( @cmd);

While INFORMATION_SCHEMA is part of the ANSI standard, using the SQL Server catalog views is usually the preferred way to go anyway.

---

One more thing: If INFORMATION_SCHEMA returns incorrect results, you might have some kind of corruption going on, you should check that by running DBCC CHECKDB in that database.