Search for an object in SQL Server

Photo by Oleg Ivanov on Unsplash

If you have a big database structure with lots of databases, plenty of tables, views, stored procedures, functions, etc., sometimes you are lost in all the objects in your server. The good news is that there are ways to handle this situation.

sys.objects view

You can easily find an object in your SQL Server by the object’s name or part of it.
Just query the sys.sysobjects system compatibility view for each database with the help of the stored procedure sp_msforeachdb. It is an undocumented one, but can be useful in situations like this where we want to run same command on all of the SQL Server databases with no cursor defined.

Examine the query below which will do the job:

EXEC sp_msforeachdb 
	'IF EXISTS
	(
		SELECT	1 
		FROM	[?].sys.objects 
		WHERE	name LIKE ''OBJECT_TO_SEARCH''
	)
	SELECT 
		''?''		AS DB, 
		name		AS Name, 
		type_desc	AS Type 
	FROM [?].sys.objects 
	WHERE name LIKE ''OBJECT_TO_SEARCH'''

Just replace OBJECT_TO_SEARCH with the actual object name you are interested in (or part of it, surrounded with %).

The given query returns:

• the Database name (DB) where the object lives
• the name of the object (Name). This is useful in cases where you search by part of the name.
• the type (Type) of the object – whether it is a table, view, stored procedure, etc.
  You can find all available types by executing the following query:

SELECT	 type_desc 
FROM	 sys.objects
GROUP BY type_desc

SQL Search

You can also search the server by using an external tool called SQL Search.
It’s a free add-in for SQL Server Management Studio and Visual studio by the company Redgate.,

Find out more on their web site.