Schema Search Report
Overview
Which procedure references this table? Where is that email address hard coded? Which databases have a column called SSN?
Every one of those is a search across every database on the instance, and there is no built-in way to do it. Schema Search is that search.
It differs from writing your own query against sys.sql_modules in two ways that matter.
- It searches the generated definition, not just the stored text. For a table it builds the columns, the data types and the constraints; for an index it builds the key and the included columns. So a search for a column name finds the tables that have it, not only the procedures that mention it.
- It covers twenty three object types, including the ones with no
sys.sql_modulesentry at all: tables, indexes, foreign keys, primary keys, users, roles, permissions, partition functions and schemes, sequences, and more.
Where to find it
An instance level entry. Right-click the server → Instance Level Reports → Schema Search.
The page title reads Schema Search for <server name>.
Requirements
- SQL Server 2008 or newer for most object types. Sequence needs SQL Server 2012, and is skipped silently on older instances rather than failing.
- Access to each database you want searched. Databases the connection cannot open are skipped.
Running a search
Type into the box and press Enter, or click Search.
| Control | What it does |
|---|---|
| Search | Runs the search. The box, the result count and the filters lock while it runs. |
| Results | The maximum number of rows to return: 10, 25, 50, 100, 250, 500 or 1000. Default 100. |
| Filters | Restricts the search to chosen databases, chosen object types, or both. |
| Stop | Appears while a search is running. Ends it and keeps whatever has been found so far. |
A progress bar and a label show which object types are being worked on.
What gets matched
The search term is matched with LIKE '%term%' against the object name and the generated definition. There are no wildcards to type and no exact-match option: it is a substring search, and a search for Cust finds Customer, CustomerID and AccountCustody.
Matching is as case sensitive as the database is. On the usual case insensitive collation, case does not matter. On a case sensitive collation the term is compared lowercased, so type your search in lower case there.
Which databases are searched
Every database on the instance that is online, not read only, not tempdb, and that the connection can actually open.
Read only databases are excluded because the search builds a temporary table inside each database as it goes. That is worth knowing if a database you expected to see is missing from the results: check whether it is read only or in a state other than online before assuming the search missed it.
master, model and msdb are searched, which is often useful and occasionally noisy.
The Results limit is a stopping condition
The number in the Results box is not a display limit, it is when the search stops. Once that many rows have been found, every worker stops and the remaining object types and databases are never looked at.
That makes searches fast, and it means a result set that is exactly the size of the limit is probably incomplete. If you get 100 rows with the limit at 100, raise it and search again.
The rows you get first are also not ranked. They are whichever object types finished first, so a truncated result is an arbitrary sample rather than the best matches.
Filters
Filters opens a dialog with two independent lists.
| List | What it does |
|---|---|
| Databases | Search only the databases in this list. Empty means every database. |
| Object types | Search only these types. Empty means all twenty three. |
Both lists have add, add all, remove and remove all. The settings are saved: the database list is remembered per instance, and the object type list is remembered globally across instances.
Filtering by object type is the single biggest speed win on this page. Each object type is a separate pass over every database, so cutting from twenty three types to two cuts the work by roughly the same proportion.
The object types
Table, View, Index, Primary Key, Foreign Key, Stored Procedure, Function, Trigger, Database Trigger, Schema, Synonym, Sequence, Rule, Default, Assembly, Partition Function, Partition Scheme, User, Role, User Permission, Message Type, Contract and Xml Schema Collection.
Reading the grid
| Column | What it is |
|---|---|
| Database Name | Which database the object is in. |
| Object Type | Which of the twenty three types it is. |
| Object Name | The object. |
| Object Definition | The generated definition, which is where the match usually is. |
Double-click a row to open the full definition in a window you can read, scroll and copy from. The grid column is one line; most definitions are not.
How to read the report
- Search narrow first. A short term across every object type on a large instance is a lot of work for a lot of noise.
- Filter to the object types you actually want. Searching only Stored Procedure and Function answers where is this called far faster than searching everything.
- Check whether you hit the limit. Rows equal to the Results setting means there may be more.
- Double-click rather than squinting at the grid. The definition column is where the answer is and one line of it is rarely enough.
- Use Stop rather than waiting. A search that is clearly finding the wrong thing does not need to finish.
Common patterns
Looking for who uses a table before dropping it. Filter to Stored Procedure, Function, View and Trigger, and search the table name. Remember that dynamic SQL that builds the name at run time will not be found by any search of this kind, here or anywhere else.
Looking for a column across the estate. Leave the object types at Table and Index. The generated definition carries the column list, so this works where a search of sys.sql_modules alone would find nothing.
Looking for a hard coded value. A connection string, a server name, an email address, a magic number. Search Stored Procedure, Function, View, Default and Rule.
Auditing permissions. Filter to User, Role and User Permission and search the login or role name.
Finding leftovers from a migration. Search the old server name or the old database name across everything. Synonyms and linked server references show up here.
Performance and what it does to the instance
This is the heaviest read-only page in the product, and it is honest about that: there is a Stop button because there needs to be one.
The work is one query per object type, and each of those queries walks every database on the instance building object definitions as it goes. The object types are handed to four worker threads so they run together rather than one after another, and each worker opens its own connection.
Nothing is written to any user database. The queries build temporary tables in tempdb and drop them.
Run a wide search on a busy production instance deliberately rather than casually, and use the filters. A search limited to two object types and three databases is a different proposition from one across everything.
Where the data comes from
sys.databasesfor the list of databases to walk.- Per object type, a query defined in the object catalogue shared with Schema Drift, run inside each database in turn, which builds the object’s definition and puts it in a temporary table before the match is applied.
Nothing is stored by this page, and no results are cached. Each search runs fresh.
Related reports
| Report | Why you would go there |
|---|---|
| Structure Change Log | When an object you just found was created or last altered, and by whom. |
| What Changed | What has changed on the instance recently. |
| Deprecated Features | Which deprecated syntax is being used, when you are searching for it to remove it. |
| Table Use | Whether the table you were about to drop is actually being read. |
| Orphan Users | The follow-up when searching for a user or login turns up something odd. |
| Large Tables | The size side of the object you just found. |
Frequently asked questions
Why is a database missing from the results? It is offline, read only, in a state other than online, is tempdb, is excluded by a database filter, or the connection cannot open it. Read only databases are excluded because the search needs to create a temporary table inside each database.
Why did the search stop early? Because it hit the Results limit. That number is a stopping condition, not a display limit.
Can I use wildcards? No, and you do not need to. Every search is already a substring search.
Does it find dynamic SQL? Only the literal text that is stored. A procedure that builds a table name from variables at run time contains no such text and cannot be found by any tool searching definitions.
Why is it slow? Because it is walking every object of every searched type in every database. Filter the object types.
Are my filters remembered? Yes. Database filters are saved per instance and object type filters are saved across instances.
Does it change anything? No. It reads, and it creates and drops temporary tables in tempdb.
Why does the same object appear more than once? Because it matched under more than one object type, for example a table and its primary key.