Easy way to search for database objects

 If you are a database administrator, you come across thousands of database objects like stored procedures, tables, views, functions, triggers, etc. So, it's not possible to remember name of each of these objects. So, how will you find out a particular object?

 

In SQL Server 2000, there is good search facility for searching database objects. It can be launched by pressing F4 in SQL Query Analyzer or from Tools -> Object Search -> New.

The screen shot of Object Search window is shown below.

 

 

You can type name of object or part of name of object included between % signs in Object name filed and check the type of objects like User table, Stored procedures and finally click Find Now to get the search results. The interesting thing in search result window is that you can right click in particular object and select option to delete or script object.

 

You can search for stored procedures and functions by using keyword sp_stored_procedures like given below. It works for current selected database only.

 

sp_stored_procedures '%rep_proc%'

 

This command when executed in query browser will list out stored procedures and functions with name containing word rep_proc. If sp_stored_procedures is executed alone without any arguments, then it will list out all stored procedures and functions of current database.

 

Also, you can search for tables and views by using keyword sp_tables like shown below. It also works for current selected database only.

 

sp_tables '%tmp%'

 

This command when executed in query browser lists out tables and views with name containing word tmp. If sp_tables is executed alone without any arguments, then it will list out all tables and views of current database.