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
- Each level of the command adds quotes: the command is a string passed to
sp_MSforeachdb, and theprintargument is itself a string. sysobjectslists user tables (xtype=U) and stored procedures (xtype=P). The first pattern only searchessys.tables.- The procedure is undocumented, so check the results on each SQL Server version you use.