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.
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
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.