Search for an Object in SQL Server

This article helps you in understanding how to Search for an object in SQL Server using T-SQL script. When we need to search for an object inside a database we can do that using sys.objects but when it comes to cross database search that needs a solution. There are different ways to search for an object in SQL Server:

T-SQL Script: A native T-SQL script.
Object Explorer Search: User required proper permissions to use this
Red Gate SQL Search: It’s not always possible installing freeware on enterprise environments
SSMS Tools Pack Add-in: An extra tool has to be added to SSMS

Why Instance level object search is required?

There are few cases where we search for objects across the instance.

Cleanup: This is the common task that every DBA / Developer performs. We usually create backups for tables for temporary basis while performing a critical update or working on a change requests. But we forget to cleanup / remove those objects.
Critical times: When there is an incident happened and you are working on resolution someone asked you details of an object path.
Developer / DBA are new to environment: When you are new to the environment and you wanted to quickly know the object path.

Search for an object in SQL Server using T-SQL:

Now we are going to create a stored procedure that helps us to search for an object name in SQL Server instance level. Remember the same script you can remove procedure and use as a T-SQL batch script.

/***********************************************************************/
--ProcName: usp_ObjectSeek
--Description: To search for an object in SQL Server instance level
--@ObjName: Name of the object to be searched
--@SearchType: Search Criteria; Default to equal
--	            equal: Entire object name should be macthed
--                  start: Object Name should starts with this name
--	            end: Object Name should end with this name
--	            contain: Object name should conatin this name 	 
/***********************************************************************/
CREATE PROCEDURE usp_ObjectSeek (@objName VARCHAR(200),@SearchType VARCHAR(20) = 'equal')
AS
BEGIN
	SET NOCOUNT ON;

	-- Declare sql to hold dynamic query
	DECLARE @SQL VARCHAR(4000);

	--Declare a table variable to hold the resultset
	DECLARE @ObjSeek TABLE (ID TINYINT IDENTITY,
				DBName VARCHAR(100) NOT NULL, 
				Obj_Name VARCHAR(100) NOT NULL,
				Obj_Type VARCHAR(50) NOT NULL);

	-- Change the search predicate based on the input
	SELECT @SearchType = LOWER(@SearchType)
	SELECT @objName =CASE	WHEN @SearchType = 'contain' then '%'+@objName+'%'
				WHEN @SearchType = 'start' then @objName+'%'  
				WHEN @SearchType = 'end' then '%'+@objName  
				WHEN @SearchType = 'equal' then @objName  
			 END;
	-- Create a dynamic query to seek for the given object
	-- "?"  indicates the db name
	SET @SQL = 'SELECT  ''?'' 	as DBName, 
			     Name 	as Obj_Name, 
			     type_desc	as Obj_Type 
		    FROM   [?].sys.objects 
		    WHERE  name like'''+@ObjName+'''';
			
	-- Search all databases in the current instance
	-- Insert details into ObjSeek table Variable
	INSERT INTO @ObjSeek (DBName,Obj_Name,Obj_Type)
	EXEC sp_msforeachdb  @SQL;

	-- Get the actual result set
	SELECT * FROM @ObjSeek;

	SET NOCOUNT OFF;
END

Execution:

Now we will see how to execute this procedure to search for an object for different scenarios:

-- Retrive all objects named as "test" from all databases
EXECUTE usp_ObjectSeek 'test','equal'

-- Retrive all objects where name ends with "_bkp" from all databases
EXECUTE usp_ObjectSeek '_bkp','end'

-- Retrive all objects where name starts with "dump_" from all databases
EXECUTE usp_ObjectSeek 'dump_','start'

-- Retrive all objects where name contains the word "temp" from all databases
EXECUTE usp_ObjectSeek 'temp','contain'