How to Find a SQL Server Database Object

How many times, we as a SQL developer or DBA, find ourselves shuffling through objects in Enterprise Manager, or expanding the left pane of Query Analyzer, trying to find a table or view for which we have no clue, except a nearly correct name, and the only way we would know that it is the right object is looking at its meta data or text. Well, it might not be an every day kind of thing, but it does happen from time to time (or perhaps not in an idealistic situation where all databases are well documented and all names follow a well defined naming convention with no exceptions, and most of all, the employees never quit).

A better why to find a SQL Server object, such as a table, a procedure, or a trigger, would be to query the sysobjects system table in the local database (of course, one has to be certain about which database that object is supposed be in).

For example:

Select * From sysobjects Where name like ‘ClientInvoice%’

(Script I)

Executing the above query displays all the objects in current database whose name starts with "ClientInvoice". If the type of the object to be searched is known, then the query can be changed to provide only that type of object whose name start with "ClientInvoice". This might return a much smaller and more readable resultset.

For example:

Select * From sysobjects Where xtype = ‘U’ And name like ‘ClientInvoice%’

-- ‘U’ for user table

(Script II)

The main shortcoming of above methods is that the sysobjects table is database specific. If one does not know which database contains the object, then the above query has to be run in all the databases to find the object.

Is there an easier way to write a query which searches all the databases in a single step to locate a specific object and/or of a specific object type? The answer is yes, by using the handy sp_MSforeachdb procedure.

For example:

Exec sp_MSforeachdb 'Select * From ?..sysobjects where xtype= ''U'' And name like ''ClientInvoice% '''

(Script III)

Sp_MSforeachdb is an undocumented (also means unsupported) procedure available in both SQL Server 7 and SQL Server 2000. It takes one string argument, which in our case is same as Script II, but there is one important difference, if we look carefully at Script III, it has “From ?..sysobjects” instead of simply “From sysobjects” as in Script II.

Why ? This is important, because sp_MSforeachdb uses dynamic SQL internally, and “?” is the placeholder for the name of the database, it keep substituting “?” with the name of each database as it loops through all the database names, thereby accessing the sysobjects table in each database in a cycle, in sequence. Suppose if there are n databases, if we do not supply “?” than sp_MSforeachdb of-course loop through the n databases but keep accessing sysobjects table of the current database (that is the database we are running this query in) n-times.

Now that we know “?” is, the placeholder for the name of database, why not try to write a script which could provide a result set with name of database, name of object, and type of object.

-- Part 1
Declare @sqlstr nvarchar(200)

-- Part 2
/* drop the temporary table if already exists */
If Object_Id(‘tempdb..#tblDBObjects') is Not Null
Drop table# tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)

-- Part 3
/*assign string value to variable */
Select @sqlstr = 'sp_msforeachdb ''Insert tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
/* execute SQL string */
Exec sp_executesql @sqlstr

-- Part 4
/* select from temp table */
Select * From #tblDBObjects Where name like ‘ClientInvoice%’
RETURN

(Script IV)

Explanation of the Above Script

Part 1 of the script simply declares variable with the nvarchar datatype. This is because the string, which is to be executed with the sp_executeSQL procedure, must be of nvarchar type.

Part 2 checks to see if the temporary table with the name tblDBObjects already exits. If temporary table tblDBObjects exits, it drops it. Then it creates a temporary table with the name #tblDBObjects. ‘#’ tells that the table should be temporary, so is created in the tempdb database. A temporary table is automatically dropped once the script completes execution successfully.

Part 3 create a SQL string which inserts the values in #tblDBObjects as it loops through databases and select values from the sysobjects table. The reason for using this string and sp_ExecuteSQL is, that it could enable us to provide object type as an input in case we want to write a stored procedure and pass the object name, as well as object type, as input parameters. Providing object types would extract smaller result set and might also speed up operation where there are too many enormous databases. This has been explained in Script V.

Part 4: Once the temp table has been populated, records can be pulled out as needed.