• SQL Search
• Database Development in SSMS

Searching for Objects in SQL Server Databases

I’ll explain how to get started with SQL Search, performing basic searches for individual words, phrases, or code fragments, and how you can limit your searches to specific object types. I’ll also demonstrate how to search using wildcards, such as to find views, procedures, and functions that reference specific system catalog views, or those that use large or deprecated datatypes.

Getting started with SQL Search

Redgate’s SQL Search offers an easy-to-use interface for finding objects in a SQL Server database and then navigating to those objects in Object Explorer. It lets you search across multiple databases on a SQL Server instance. The way in which you get started with SQL Search depends on whether you’re using SSMS or Visual Studio:

• To use SQL Search in SSMS, you must first download and install the product. You can then launch SQL Search from the SSMS toolbar.
• To use SQL Search in Visual Studio, you must first install SQL Server Data Tools (SSDT), which is included in the Data Storage and Processing workload. You can then add the SQL Search extension. After you’ve added the extension, you can launch SQL Search from the Tools menu.

You’ll find more information about installing and running SQL Search in the product documentation. You should review this information before trying to set up SQL Search so you’re familiar with the system requirements and installation process.

The following figure shows SQL Search as it appears in SSMS (it looks very similar in Visual Studio), having searched for the text “customerid” across all databases on my local instance:

SQL Search opens in its own tab in SSMS and Visual Studio. The tab consists of a search bar, results pane, and preview pane. On the search bar, you enter a search term and choose the search criteria for narrowing down your search (more on that in a bit.)

The search results are displayed in the results pane and include a list of the database objects that meet the search criteria. When you select an object in the results pane, the object’s text is displayed in the preview pane. The text varies depending on the type of object selected. For example, if you select a stored procedure (as in the figure above), the preview pane displays the procedure’s T-SQL code, but if you select a table, the preview pane lists the table’s columns and their data types. The pane also highlights each instance of the search term within the object’s text.

The search bar lies at the heart of SQL Search. It includes a text box for entering a search term, two check boxes for controlling how the term is treated, and several drop-down lists for refining your search.

In the previous figure, I’ve outlined the search features in red rectangles and assigned labels that correspond to the following descriptions:

• Search term. Enter the word or phrase on which to base your search. The more precise the search term, the more limited your results. You must enter at least two characters to return any results.
• Exact match. Select the Match whole words only check box if you want to limit the search results to those that match the search term exactly, whether the term is a single word or phrase. You cannot use this option with the Use wildcards option.
• Wildcards. Select the Use wildcards check box if you want to use wildcards in your search term. You cannot use this option with the Match whole words only option. SQL Search supports the following wildcards:
  • Use an asterisk (*) to match zero or more characters.
  • Use a question mark (?) to match exactly one character.
  • Use square brackets ([]) to match one character from a list of characters or range of characters.
  • Use a caret (^) to match on any characters except for the bracketed characters preceded by the caret, as in [^0-9].
  • Use square brackets to escape a wildcard character such as an asterisk, as in [*]. You cannot use this method to escape the square brackets themselves.
• Object types. Select one or more database objects on which to base your search. SQL Search supports the following object types:
  • Tables
  • Views
  • Procedures
  • Constraints
  • Triggers
  • Functions
  • Other
  • Jobs
• Context types. Select one or more context types to focus your search on specific object elements. SQL Search supports the following context types:
  • Name (applies to all object types)
  • Text (applies to all object types except tables)
  • Column (applies only to tables)
• Databases. Select one or more databases to target your search. The databases are those available on the selected SQL Server instance.
• Servers. Select a SQL Server instance to target your search.

The options are fairly self-explanatory and straightforward to use. For example, to restrict your search to certain object types, you would click the All object types box, select or clear the check boxes associated with each object type, and then click OK.

What objects does SQL Search index?

For each SQL Server instance, SQL Search indexes only those objects returned by the following catalog views:

• sys.objects
• sys.columns
• sys.sql_modules
• sys.check_constraints
• sys.default_constraints
• sys.computed_columns

This covers all the Object Types listed explicitly in the previous section, plus some that fall into the Other object type. This refers to anything not included in the other categories, but this does not apply to all objects, such as roles, certificates, extended properties or full-text catalogs.

If SQL Search does not find an object in one of these views, the object won’t be indexed and therefore won’t be included in the search results. It doesn’t for example, look in sys.indexes, so search for specific indexes won’t work, although it does cover table constraints such as PKs and FKs, since these are included in sys.objects.

Note that SQL Search also filters out the square brackets around identifiers when indexing the objects to help speed up searches.

Performing a basic search

To perform a basic search, you select the target object types, context types, databases, and instance and then type the search term in the text box on the search bar. You can also type the search term first and then configure the other settings. SQL Search continuously updates the results as you modify the search options. However, SQL Search doesn’t start displaying the results until you type at least two characters in the text box. It then continues to refine the results as you type more characters. The more complete the search term, the more exact your results.

Searching for columns

By default, SQL Search returns results for all object types, context types, and databases, but you’ll no doubt want to limit your search to specific combinations of these options. For example, suppose you’ve installed the WideWorldImporters sample database on a local SQL Server instance and now want to search for tables whose column names include the term customerid. To search for these tables, you can use the following search criteria:

• Search term: customerid
• Object type: Tables
• Context type: Column
• Database: WideWorldImporters
• Server: localhost (or named instance)

In this case, SQL Search returns the seven tables shown in the following figure. As expected, each table includes at least one column whose name contains the term customerid. (I’ll be discussing how to work with search results in the next section.)

A column name needs only contain the search term for the table to be included in the results. It does not have to be the exact term. For example, the results include the Customers table, which is selected in the figure. The table includes the CustomerIDcolumn and the BillToCustomerID column. Both column names satisfy the search criteria, as evidenced by the highlighted search term in the preview pane. Even if the table included only the BillToCustomerID column, it would still meet the search criteria. (Later in the article, I’ll explain ways to achieve more precise results.)

Searching for objects that reference a column

Now suppose you want to search views, procedures, and functions to determine which ones reference the LogonName column in the Application.People table. For this, you can use the following search criteria:

• Search term: logonname
• Object types: Views, Procedures, Functions
• Context type: Text
• Database: WideWorldImporters
• Server: localhost (or named instance)

You must specify the Text context type even though you’re searching for a column name. As noted earlier, the Column context type applies only to tables. For other object types, you need to search the T-SQL code (text) for the term even though it’s in reference to a column. However, this means the search results will include any object whose definition text contains the term logonname, no matter how the term is used. The following figure shows the search results, which include three objects, all stored procedures.

Limiting searches by schema

At times, you might want to search only the object names, rather than the text, in which case, you can use the Name context type. For example, you might want to return a list of the views, procedures, and functions in the Website schema. Unfortunately, SQL Search doesn’t let you filter the results by schema. To get around this, you can use the following search criteria:

• Search term: website. (including the period)
• Object types: Views, Procedures, Functions
• Context type: Name
• Database: WideWorldImporters
• Server: localhost (or named instance)

By specifying website. as the search term, you can easily view a list of objects in the Website schema because each object name begins with this term. The following figure shows the search results, which include several views, one function, and multiple procedures—all within the Website schema.

By adding the period at the end of the search term, you’ve eliminated any objects that might include the term website in their name, unless the objects happen to be in the Website schema. For example, the ActivateWebsiteLogon procedure is included in the results because it is in the Website schema, not because it includes the term website in its name.

Working with the search results

When you search for objects in SQL Search, those that meet the search criteria are displayed in the results pane. As noted earlier, SQL Search automatically updates the results as you type your search term or adjust your search options. SQL Search displays the results in six columns:

• Object name
• Schema
• Database
• Type (object type)
• Matches on (context type)
• Detail (object text, if applicable)

You can sort the results by any of these columns by clicking the column header. If you click the column header a second time, SQL Search reverses the sort order.

SQL Search displays data in the Detail column only for some object types. For example, the column usually displays information for views, procedures, constraints, triggers, and functions. I say usually because I came across a couple procedures that included no information in the Detail column.

You might also run into mixed results for constraints. For instance, SQL Search includes information in the Detail column for defaults and check constraints but not for primary keys, foreign keys, or unique constraints. Chances are, you’re not going to rely on the Detail column to any great degree because it’s easier to read the text in the preview pane, but you should be aware of the differences, especially if you sort the results by that column.

To view an object’s text in the preview pane, you need to select the object in the results pane. The text might be the object’s name, its T-SQL code, or a list of columns, depending on the selected object type. For example, the following figure shows a search that specifies customerid as the search term. The search criteria include all object types and context types. One of the objects returned by the search is the Configuration_EnableInMemory procedure, which is selected in the results pane. Because it is selected, the procedure’s T-SQL is displayed in the preview pane. (Also notice that there is no Detail information for a couple procedures in the results pane.)

The search term customerid appears in multiple places in the Configuration_EnableInMemory procedure. SQL Search highlights all instances of the search term and automatically scrolls to the first instance when you select the object. However, SQL Search provides no count for the total number of search term instances within an object’s text, nor does it allow you to jump from one instance to the next. To find all instances of the search term, you must scroll through the text manually.

That said, SQL Search does make it easy to locate an object in Object Explorer directly from the search results. To do so, use one of the following methods:

• Double-click the object in the search results.
• Right-click the object in the search results and then click Select in Object Explorer.
• Select the object in the search results and then click Select object in Object Explorer at the top of the preview pane.

The following figure shows how SQL Search located the Configuration_EnableInMemory procedure in Object Explorer.

In some cases, SQL Search is not particularly good at locating an object in Object Explorer. For example, when I tried to locate a foreign key constraint in Object Explorer, SQL Search expanded every table in the database without locating the key itself, which turned out to be more of a nuisance than a help. In some cases, it’s still easier to locate the object manually.

SQL Search also doesn’t provide a particularly elegant way to export the search results. Your only option is to manually copy one or more objects in the results pane and then paste them to another application. To copy result information, select one or more objects, right-click the objects, and then click Copy. You can also use familiar keyboard shortcuts such as Ctrl+C. Once you’ve copied the data (which is in a tab-delimited format), you can then paste it to your target location.

I found it easiest to copy the data directly into an Excel worksheet and then to manipulate it in there. In Excel, I can easily delete unnecessary columns and rows and then sort the data by one or more of the remaining columns, allowing me to use the data for further processing or analysis.

Performing an exact search

There will no doubt be times when you’ll want to return more precise results than what you can achieve with a basic search, in which case, you might benefit from using the Match whole words only option, which limits the results to those objects that match the search term exactly.

For example, suppose you want to search across all object types and context types in the WideWorldImporters database to find instances of search term name. If you run a basic search, the results will include 80 objects, as shown in the following figure.

The challenge with this approach is that it returns all objects that contain the term name, regardless of how the term is used. For instance, the results can include objects that contain elements such as @RoleName, sysname, or QUOTENAME. This might be fine for some searches, but often it is not.

If you limit the results to the exact search term by selecting the Match whole words only option, then the same search returns only 13 objects, as shown in the following figure. In this case, I’ve selected Configuration_RemoveAuditing procedure, so its text displayed in the preview pane. Notice that the term name is now highlighted only when it stands alone.

You can also use the Match whole words only option when your search term is a phrase rather than a single word. For example, if you were to perform a basic search on the phrase for json in the WideWorldImporters database, the results would include any objects that contain both the term for and the term json, although not necessarily together. However, if you’re specifically looking for instances of the FOR JSON clause, you can select the Match whole words only option, which gives you the results shown in the following figure.

In this case, the search criteria specify the Procedures object type and Text context type. As a result, SQL Search returns six procedures, all of which contain the FOR JSON clause. If the Match whole words only option had not been enabled, the results would have included several more procedures. This might not be a big deal for a smaller database, but for a larger one, the difference in the number of results could have been significant.

Performing a wildcard search

SQL Search also supports the use of one or more wildcards in your search term via the Use wildcards option. You can perform some useful searches in this way, such as to find:

• Objects that contain a specified term – such as tables within a certain schema that contain the term “orders”
• Views, procedures, and functions that reference specific types of objects, such as certain system catalog views
• Objects that use specific data types, such as datetime

Single character searches

The simplest wildcard is the question mark (?), which represents exactly one character. For example, you might want to find all procedures whose names include an underscore. In SQL Search, however, you must specify at least two characters to return any results. To get around this, you can specify _? as your search term, giving you the results shown in the following figure.

Find all objects that contain a specified term

SQL Search also supports the asterisk (*) wildcard, which represents zero or more characters, similar to how the wildcard is used in the T-SQL LIKE clause. The wildcard can be particularly handy when you know part of the object name or code snippet or when your T-SQL conforms to specific conventions. For example, you can use the following search term to find all tables in the Purchasing schema whose names include the term order:

Notice that the wildcard comes after the schema name and period but before the term order. This means that the table name itself can start with any set of characters, or it can start with order. At the same time, there is nothing to prevent other characters from following the search term. For example, the search could theoretically return any of the following tables:

• Purchasing.Order
• Purchasing.Orders
• Purchasing.OrderLines
• Purchasing.InternetOrder
• Purchasing.StoreOrders
• Purchasing.StoreOrderShipments

In the case of the WideWorldImporters database, the search returns only two tables, as shown in the following figure. Notice that the table names include characters both before and after the term order.

Character matching

The wildcard feature in SQL Search also supports the use of square brackets ([]). One way you can use brackets is to match one character to a list of possible characters. For example, you can use the following search term to find all views, procedures, and functions that reference any system catalog views whose names start with either sys.database_p or sys.database_r:

By placing the pr within brackets, you’re telling SQL Search that the letter following the underscore must be either a p or an r. What comes after either letter doesn’t matter. The following figure shows the results returned by SQL Search.

The search returns only two procedures. The AddRoleMemberIfNonexistent procedure is selected in the figure. It’s text includes references to both the sys.database_principals and sys.database_role_members catalog views.

The brackets make it easy to narrow down your search. Without them, you would be left with sys.database_ as your search term, in which case, your search results could also include objects that reference system catalog views such as sys.database_credentials or sys.database_scoped_credentials.

Another way you can use brackets for wildcard searches is to specify a range of possible characters. For example, suppose you want to know which views, procedures, or functions use the nchar data type with a size between 10 to 99 bytes. To find these objects, you can use the following search term:

The search term includes two sets of brackets, one for each of the two digits enclosed in the data type’s parentheses. The first set of brackets species a range of 1 through 9, and the second set of brackets specifies a range of 0 through 9. This means that the search results can include objects that specify nchar(10) or nchar(18), but not objects that specify nchar(4) or nchar(100), unless they also include instances of nchar in acceptable sizes. Only two procedures in the WideWorldImporters database meet the search criteria, as shown in the following figure.

SQL Search also lets you use the caret (^) wildcard to match on any characters except those preceded by the caret. Consider the following scenario: You want to find all objects that specify datetime in their text but not those that specify datetime2 (unless they also specify datetime). You also want to include any objects that specify datetime within parentheses.

If you use datetime as your search term and select the Match whole words only option, you’ll exclude datetime2, but you’ll also exclude any references to datetime that are within parentheses. To get around this, you can instead enable wildcards and use the following search term:

The first set of brackets specifies the range a-z but precedes the range with a caret. This indicates that the character directly preceding datetime cannot fall in this range. In this way, you’re eliminating elements that include datetime as part of a larger name, such as SYSDATETIME, but still accepting non-alphabetic characters, including an opening parenthesis.

The second set of brackets also uses a caret to limit the search. In this case, the caret indicates that the character directly following datetime should not be a 2. It can be another character, such as a closing parenthesis, but not the number 2. The following figure shows the objects returned by SQL Search when this search term is used.

SQL Search returns only two procedures, one that contains datetime within parentheses and one without the parenthesis. This type of search can still give you unwanted results, but using brackets in this way can significantly reduce the number of returned objects, making it easier to find exactly what you’re looking for.

SQL Search: A good but basic search engine

If you use SQL Search for its intended use—to search SQL Server databases with minimal effort—you’ll quickly appreciate how easy it to identify and locate specific database objects. However, SQL Search is by no means an advanced search engine. It provides a simple, straightforward interface that lets you narrow down your searches in short order, but its lacks more sophisticated features, such as displaying a count of search term instances within an object or providing more effective sorting and exporting capabilities. Even so, SQL Search is a handy tool to have at your disposal, and given that it’s free, you have little to lose by trying it out. Just know what it can and cannot do, and then take full advantage of all the things it can do.

Tools in this post

SQL Search

Search within SQL Server database schemas

Find out more