question

2sasha3 avatar image
2sasha3 asked

How do I use a table of Database names to repeat a query on multiple databases?

I have a table that lists the databases on a server that I want metadata on. I want to query each of those databases to get their Schema, table and column names. The end result; I want a table that will show the following columns of data: Database Name, Schema Name, Table Name, Column Name What is the best way to do this?
servermetadata
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AaronBertrand avatar image
AaronBertrand answered
This will work on SQL Server 2005 and above: DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + ' UNION ALL SELECT [database] = ''' + name + ''', [schema] = s.name COLLATE Latin1_General_CI_AI, [table] = t.name COLLATE Latin1_General_CI_AI, [column] = c.name COLLATE Latin1_General_CI_AI FROM ' + QUOTENAME(name) + '.sys.schemas AS s INNER JOIN ' + QUOTENAME(name) + '.sys.tables AS t ON s.[schema_id] = t.[schema_id] INNER JOIN ' + QUOTENAME(name) + '.sys.columns AS c ON t.[object_id] = c.[object_id]' FROM sys.databases WHERE database_id > 4; --^^^^^ you may want other filtering -- you can also change the above query to pull the name from your table -- or at least join against sys.databases (in case your table gets out of date) SET @sql = @sql + N' ORDER BY [database],[schema],[table],[column];'; SET @sql = STUFF( @sql, 1, 11, '' ); -- you may have to adjust this number -^^ EXEC sp_executesql @sql; --- Thomas has led you to the replacement I wrote, but you can see more background on that in the following two posts: [ http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx](http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx) [ http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/][1] Whatever you do, ***DO NOT use sp_MSforeachdb***. [It's broken and Microsoft won't fix it]( http://connect.microsoft.com/SQLServer/feedback/details/264677/sp-msforeachdb-provide-supported-documented-version). [1]: http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.