Posts

Showing posts with the label Database

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.