Share via

Find a Table on a SQL Server across all Databases

  • Article

To find a table in the SQL across all databases you can use undocumented stored procedure sp_MSForEachDB.

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''

 

Alternatively, instead of using undocumented stored procedure, you can simply create a query like this:

DECLARE @SQL NVARCHAR(max)
 
SET @SQL = stuff((
            SELECT '
UNION 
SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
FROM ' + quotename(NAME) + '.sys.tables WHERE NAME LIKE ''%'' + @TableName + ''%'''
            FROM sys.databases
            ORDER BY  NAME
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 8, '')
 
--PRINT @SQL;
 
EXECUTE sp_executeSQL @SQL
    ,N'@TableName varchar(30)'
    ,@TableName = 'items'

Other Languages