Have you ever tried to look for an object in a SQL Server instance that has hundreds of databases without knowing the object's exact name and the database in which it resides? It can be quite time-consuming, especially if the databases include objects with similar names. To make the search quicker, I created sp_ObjectSearch. This stored procedure checks objects' names for the string you specify. It searches through all the objects in each database within the current SQL instance.
To call the sp_ObjectSearch stored procedure, you use the syntax
sp_ObjectSearch 'search string'
where search string is the target string. For example, suppose you need to find an object whose name includes the word access. You'd use code such as
EXEC master..sp_ObjectSearch 'access'
Figure 1 shows sample results from this query.
Figure 1: Sample results from the sp_ObjectSearch store procedure |
 |
As you can see, six databases contain objects whose name includes the string access. Besides specifying the database's name and the object's name, the result set specifies the type of object. The sp_ObjectSearch store procedure handles many types of objects, including user-defined tables, SQL stored procedures, views, and primary key and foreign key constraints. As Listing 1 shows, it uses a simple CASE function to identify each object's type. (For information about both simple and searched CASE functions, see "T-SQL 101: The CASE Function".)
Listing 1: Case Function that Identifies the Type of Object |
 |
The sp_ObjectSearch stored procedure works on SQL Server 2005 and later. For backward compatibility, I created sp_ObjectSearch_2K. You can download both stored procedures by clicking the 101693.zip hotlink at the top of the page.