Need to Find an Object But You Don't Know Its Exact Name? Try this Search Engine

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 query

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".)

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.