LLI Wiki — My Digital Knowledge Base

Find a table or object across all databases in SQL Server

Search for a table or object name in every database of a SQL Server instance using sp_MSforeachdb.

sp_MSforeachdb is an undocumented procedure that runs a command in each database. The ? in the command is replaced by each database name. The two patterns below search every database of an instance.

Find tables whose name contains a text

EXEC sys.sp_msforeachdb 
    'SELECT ''?'' DatabaseName, Name FROM [?].sys.Tables WHERE Name LIKE ''%product%'''

Find an object in every database

The command below prints, for each database, a SELECT on sysobjects that looks for the object name. Copy the printed statements from the Messages tab and run them.

sp_MSforeachdb 'print (''select ''''?'''', * From ?..sysobjects where xtype in (''''U'''', ''''P'''') And name = ''''MyProcedure'''''')'

Notes

Mastodon LLIMastodon SocialFosstodon