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. 

1 comment:

Fire Dragon said...

Nếu bạn đang cần chuyển hàng từ thái lan về việt nam hãy liên hệ với chúng tôi. Chúng tôi sẽ giúp bạn mua hàng thái lan online một cách dễ dàng. Chỉ cần đưa thông tin sản phẩm bạn cần cho chúng tôi, chúng tôi sẽ mua và vận chuyển về Việt Nam. Khi chỉ là mỗi Thái Lan chúng tôi còn có các dịch vụ khác như: dịch vụ chuyển hàng từ mỹ về việt nam, dịch vụ vận chuyển hàng đi campuchia, dịch vụ đặt hàng quảng châu giá rẻ, .... Nếu bạn cần mua hàng trung quốc giá rẻ hay cần mua hàng trên taobao hãy liên hệ và sử dụng
dịch vụ order hàng trung quốc của chúng tôi. Chúng tôi sẽ giúp bạn
chuyển hàng trung quốc về việt nam một cách nhanh chóng và tiện lợi nhất.