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

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.

Here's a Stored Procedure May Want to Use to Find Objects

Create PROC FindObject_usp 
(
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
-- Insert wildcard, if exact search is not required.
-- Set @objname = '%' + @objname + '%'
-- Its better to supply custom wild card in the input parameter @objname
/* 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)
)
Begin
If @objtype = 'CHECK'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''C'''''''
If @objtype = 'Default'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''D'''''''
If @objtype = 'FOREIGN KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''F'''''''
If @objtype = 'Log'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''L'''''''
If @objtype = 'Scalar function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''FN'''''''
If @objtype = 'Inlined table-function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''IF'''''''
If @objtype = 'Stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''P'''''''
If @objtype = 'PRIMARY KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''PK'''''''
If @objtype = 'Replication filter stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''RF'''''''
If @objtype = 'System table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''S'''''''
If @objtype = 'Table function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''TF'''''''
If @objtype = 'Trigger'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''TR'''''''
If @objtype = 'User table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''U'''''''
If @objtype = 'UNIQUE constraint'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''UQ'''''''
If @objtype = 'View'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''V'''''''
If @objtype = 'Extended stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects where xtype = ''''X'''''''
If (@objtype = '') Or (@objtype is Null)
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as 
DBName, name, xtype From ?..sysobjects'''
End
/* execute SQL string */If (@sqlstr <> '') Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* If @objname is not supplied it should still return result */If (@objname = '') Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN

(Script V)

The above script creates a stored procedure which takes two optional parameters, @objname (name of the object to be searched) and @objtype (type of the object to be searched). Different types of object type and their abbreviations can be found in SQL online help for sysobjects string). Stored procedure FindObject_usp creates different SQL string based on different object types, e.g., @objtype parameter, if @objtype is not provided is selects all the objects from sysobjects table and inserts into #tblDBObjects temp table. It is evident that incase of enormous databases, if object type is known, providing @objtype parameter makes query much faster. Once #tblDBObjects table has been populated it can be queried with @objname parameter with or without a wild card as needed.

We can execute the FindObject_usp procedure, for example, to find a object of type Check constraints whose name starts with ‘CK_B’ as;

Exec FindObject_usp 
'CK_B%', 'check' 

Or

Exec FindObject_usp1 
'xp_%', Null

Conclusion

I assume that this procedure would be used by database administrators on an as needed basis. This is not something which would run on database servers most of the time, but if performance is an issue, you could substitute the use of a temp table with a table datatype. Once the procedure is in place somewhere in a dbadmin or similar database where DBA scripts are kept, you can start using it, which would hopefully make DBA’s life much easier.

About Author

Ashish holds a BSE in electronic engineer, and has 11 years of computing-specific experience, including varied experiences from client/server design and development, to websites design and development & database administration to system administration. He has been working with design, development and administration of SQL Server for 6 years. He has MSCE, MCDBA, and MCT certifications and works as independent consultant and trainer.

Ashish can be reached at ashish@4GLtech.com

Published with the express written permission of the author. Copyright 2004.