How to search for objects on all the SQL Server databases

Last updated on November 4th, 2024

You can seach for objects in all the SQL Server databases within one instance by using the built-in stored procedure: sp_MSforeachdb. For example, let say that you are looking for a stored procedure called: uspGetManagerEmployees but you don’t know in what databases it was created. You can use the following 3 lines to find it:

DECLARE @command varchar(1000);
SET @command = 'USE ? select DB_NAME() dbname, p.* from sys.procedures p where name like ''%uspGetManagerEmployees%''';
EXEC sp_MSforeachdb @command;

Similarly, you can search for tables, columns, etc. For example, the following script is looking for a table called SalesPerson:

DECLARE @command varchar(1000);
SET @command = 'USE ? SELECT c.* from INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME like ''SalesPerson'''; 
EXEC sp_MSforeachdb @command;

Leave a Comment

Your email address will not be published. Required fields are marked *