
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.
One thought on “Search for an object in SQL Server”