Pages

Google Analytics Alternative

Search This Blog

Friday, December 16, 2011

How to find a table when you don't know which database it belongs to?

In this very short tip I'd like to share my solution to a yesterday quesiton in MSDN forum - how to find the database the table belongs to. I use my favorite idea of dynamically constructing a query using INFORMATION_SCHEMA.TABLES view.

DECLARE @table sysname, @SQL NVARCHAR(MAX)

SET @table = 'Items'

SET @SQL = ''

SELECT @SQL = @SQL + ';

IF EXISTS (SELECT 1 from ' + QUOTENAME(name) + '.INFORMATION_SCHEMA.Tables WHERE Table_Name = @table

AND TABLE_TYPE = ''BASE TABLE'')

PRINT ''Table ' + @table + ' found in ' + name + ''''

FROM sys.databases

 
EXECUTE sp_executeSQL @SQL, N'@table sysname', @table
That's all. Try it with the table which you may have in multiple databases. I used a bit undocumented way of concatenating multiple rows in a string, so I can not actually use ORDER BY in this code. To be strict, I need to use XML PATH('') approach here instead.

No comments: