Searching a table, sp etc 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. 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s