How to search for objects on all the SQL Server databases

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;