Ed Pollack

Searching SQL Server made easy – Searching catalog views

March 9, 2016 by

The need to search through database schema for specific words or phrases is commonplace for any DBA. The ability to do so quickly, accurately, and completely is critical when developing new features, modifying existing code, or cleaning up the vestiges from an application’s ancient history.

In this article, we review system catalog views and demonstrate how to use them in order to gather useful information about a wide variety of schema, objects, and components of your SQL Server. Some of the scripts presented here, such as those for foreign keys and indexes, will be extremely valuable tools on their own, without being used as part of a schema-search framework.

Background

Nearly anyone in the software development field has a continuously growing and evolving set of tools that they rely on regularly. One of these tools that I turn to almost daily is the ability to quickly search through database schema. Many operations may lead me to need this tool, some include:

  • When dropping an old stored procedure, we want to check for other TSQL/database references.
  • We’re adding a new column to a table and want to verify any objects that reference it table directly.
  • We want to change the data type of a column and evaluate all places it is used to change the type there as well.
  • When migrating a database to another server, we need to locate and update all references to it.
  • A stored procedure is returning bad data. We want to find other stored procs with the same broken logic.

What we want is a readily available, easily customizable tool that can search through all types of objects, whether they be tables, views, linked servers, or indexes and return any objects that contain our search criteria. In the spirit of sharing things I love to use, this article will serve as an introduction to a variety of system/catalog views and how we can use them in order to learn a great deal about our server using minimal effort on our part.

The Building Blocks of a Search Solution

The list of objects that we want to search in SQL Server is long, but many require only a few lines of TSQL in order to determine if a search string is found or not. We’ll subdivide the remainder of this article into sections based on the objects we are searching and bring it all together at the end into a single script that will do our bidding.

If you have a short attention span, or are already very familiar with the various system catalog views, feel free to skip ahead to where we bring it all together. The following sections explain all of the TSQL that will be used in the final script, and are provided as a solid reference to support what may otherwise seem a somewhat cryptic 250+ lines of TSQL. Some of these queries—especially those for indexes and foreign keys are extremely useful tools on their own…TLDR at your own risk!

Database Names

This is straight-forward, but certainly shouldn’t be ignored. If our search string happens to be found in the name of a database, we want to know about it! This can be accomplished using sys.databases, just like this:

Sys.databases includes a row for every database on the server, regardless of its current status. We’re including an object type in each query so that when we combine all of this TSQL later on, we’ll be able to tell the difference between different types of objects that happen to share the same name. On my local server, the results of the above query look like this:

This is a server-wide search and only needs to be run once and will return information on all databases on your SQL Server.

Logins

Another quick & easy server-wide search is for server logins. We can similarly check for our search string using sys.syslogins:

This will return a row for any server login that contains our search string in the login name. In the case of my server, we get the following two results:

The sys.syslogins view includes a column called loginname, but this is provided for backward-compatibility only and can be ignored for the sake of our search efforts.

Jobs

A big pile of data we’d like to dig through are SQL Server Agent jobs. If our search text is located within a job, its description, or within any of its job steps, we definitely want to know about it. Job metadata is stored in MSDB, of which we will use dbo.sysjobs to gather information about job names and descriptions. dbo.sysjobsteps contains info on each job step, of which we will want to check both the step name and the contents of the command itself. We’ll run two queries that will return similar sets of job data (when the job name or description match) or job step data (when the job step metadata matches):

For our jobs, we return a bit more metadata than before, as we are interested in both the job itself, as well as the job steps associated with it. Note the use of a LEFT JOIN to dbo.sysjobsteps. In the event that a job has no steps defined, or none that match our search criteria, we still want to get search results on any matches to the job name or description. The results on my local server look like this:

Including the various columns in the WHERE clause allows us to quickly see where we matched our search terms, providing more than enough information for us to conduct whatever additional research is required.

Linked Servers

Searching for any linked servers that contain our search terms is straightforward and is also a single server-wide search:

This checks both the name of the linked server, as well as the data source definition for the search string. sys.servers contains quite a bit of additional data about our servers that can also be queried, such as security settings, provider type, and the last modified date. For the examples in this demo, I’m sticking to the basics in order to minimize complexity and the amount of data we need to collect along the way. The results of the above query are as follows:

I only have a single linked server on my local machine, but since it matches the search criteria, it’s returned as expected, along with the local instance name.

Tables

Let’s move on to database-level objects. These are all defined per-database and should be searched in every database on a server, if you’re looking to be thorough and check everywhere for your search terms. If you only care about a specific database or set of databases, then you may only check those and ignore the rest.

Searching tables is straight-forward:

sys.tables provides information on every table in the database, including system objects, and makes for an easy search:

The database name is included as a convenience, which will make locating the object easier if we are collecting lots of this type of data for inspection later on.

Columns

Searching for columns in a meaningful way involves checking back with the parent table to confirm the relationship we are identifying. Knowing that some column exists with a specific name isn’t very useful, we want to know the database, table, and column so that it’s easy to locate:

This query introduces sys.columns, which provides a row per column in each different table in the database. The results of the above query provide us exactly what we are looking for:

Every BusinessEntityID in AdventureWorks is returned, all 18 of them! We have so far been omitting schema name from the result set, but you could easily add it in to any related object query with a join to sys.schemas:

The results are the same as before, but with the added schema_name column:

The results are cut off to save some space here, but the added schema name is extremely useful in AdventureWorks as it makes heavy use of a variety of different schemas. We’ll include it in all examples going forward since it’s minimal extra effort for big informational gain!

Schemas

While we are on the topic of schemas, we may as well search them as well:

This simple query returns any schemas whose names match our search terms.

Synonyms

If you happen to use synonyms, then being able to search both their names and targets is very useful, and can be done solely with the sys.synonyms view:

The results of this query are similar in style to those for the linked server search:

Indexes

We’ll be searching indexes by two criteria: name and column list. Each requires a separate search as the method for each is quite different. Searching index names is quite straightforward:

We join sys.indexes to sys.tables in the same manner that we did for sys.columns earlier. The result is a list of any index that is named using the search terms provided:

That was the easy part! The hard part is not only searching the index column lists, but returning meaningful data about each on a single result row. Given the choice, I’d prefer to not get a row per column, which will be extremely hard to decipher. In order to accomplish this, we’ll introduce sys.index_columns, and break this view up into key columns and include columns. Once we’ve separated between the types of index columns, we will concatenate those column lists into strings. The only quick and dirty way I came up with to do this in a single query was using XML. Dynamic SQL is also an option, but would provide a much longer and more complex query to work with. For the sake of this demo, we’ll use XML, but can certainly delve into wild dynamic SQL in a future article, as I do enjoy that sort of thing!

While not as simple as our earlier queries, this gets the job done and is quite fast. The CTE is responsible for generating the key and include column lists. Once this work is done, the remainder of the query needs only to perform a comparison against those strings using out search criteria and we’re done! ISNULL is used on the include column list since an index must have key columns, but is under no obligation to contain any include columns. The results of this search on AdventureWorks are as follows:

Even on its own, this is a fairly useful tool! In addition to searching for indexes that have columns matching our search criteria, we could leave off the WHERE clause and use this TSQL to provide a complete list of all indexes in a database. This can be used for index research when looking to identify overlapping or duplicate indexes, or simply for documentation purposes.

Service Broker Queues

Finding queues is quite easy, and can be done by querying the system view sys.service_queues:

The results will be any queues in the current database matching our search criteria, including any system objects that happen to be in the result set (of which there are none here):

Constraints

Constraints are enumerated in a handful of metadata tables, and for each one we will want to check both the constraint name and the definition to verify that if our search terms are found in either. Foreign key name checking can be accomplished with this query:

Here, we grab the schema and parent table name, in addition to the foreign key name. This leaves the guesswork out of locating the key once we have a name:

Next, we’d like to verify if the search terms exist within the column list of a foreign key, which will be a similar process to what we did for index columns earlier:

The process is almost identical to before. While foreign keys typically are composed of single columns, they can contain multiples, and our goal here is to condense them into a comma-separated list for easy searching and viewing. As such, the TSQL above is very similar in form to our index column search in that we use two subqueries in order to generate column lists, and then join them to the main result set to collect the remainder of the columns we are interested in:

The results are much easier to read than the query that generated them A full list of both referenced and referencing objects are provided in order to make understanding the foreign key as simple as possible. In the unfortunate event that column names differ between parent and child, this will allow us to see both side-by-side.

Run without a WHERE clause, this query can also be a useful way on its own to quickly view all foreign keys in a database, with easy-to-read schema, table, and column lists.

It gets easier from here! Default constraints are completely defined within a single system view, sys.default_constraints. We’ll grab some additional data along the way so we know what table and column the constraint applies to, but the resulting TSQL remains relatively straight-forward:

The results are a clean list of details for each default constraint:

We intentionally check both the name of the default constraint as well as the definition, as we may want to know if a specific default value matches our search terms.

Check constraints are searched very similarly using the sys.check_constraints system view:

This is similar to what we did for default constraints. Again, we check both the constraint name as well as the definition, and include some parent info to make finding the constraint easier. The output looks like this:

DDL Triggers

DDL triggers at the server level are defined separately from everything else discussed in this article and need to be handled separately. The basic metadata can be found in sys.server_triggers and the definitions in sys.server_sql_modules:

The results are straight-forward and provide everything we need to find and potentially analyze a given trigger:

Database DDL triggers are a bit left out as well, as they get definitions in sys.sql_modules, but don’t get any associated sys.objects metadata. We’ll handle them completely separately:

This will only return database-scoped DDL triggers. Since we are getting other types of triggers and objects elsewhere, our WHERE clause filters down to database-level objects:

Stored Procedures, Views, Functions, Rules, and Triggers

We can use sys.sql_modules and sys.objects to search all of these object types, including their definitions, all at once. sys.sql_modules provides object definitions for the following types of objects (with the sys.objects type in parenthesis):

  • Stored Procedure (P)
  • Replication Filter Procedure (RF),
  • View (V)
  • DML Trigger (TR)
  • Scalar Function (FN)
  • Inline Table-Valued Function (IF)
  • SQL Table-Valued Function (TF)
  • Rule (R)

The resulting query is relatively simple compared to some of our adventures thus far:

The large CASE statement ensures that we provide a useful description of each type that we can read and understand. Feel free to adjust these if you have preferred nomenclature. The abbreviations that are checked are standard and are the same anywhere they are referenced in SQL Server’s metadata.

The results include the parent object, if one exists, which will typically only be for DML triggers, which exist on a specific table:

Please note that I have intentionally not used sys.syscomments in order to gather data about the definitions of these objects. Sys.syscomments is deprecated and will be removed in a future version of SQL Server. As always, deprecated objects do not get the same level of maintenance and care from Microsoft that other objects receive, and therefore relying on them introduces risk and potential inaccuracies into our code. Please avoid this system view at all costs, and replace it if you rely on it in any of your existing code.

These results are the last of what I would consider a standard SQL Search. The next section will cover the less common use cases of SSIS and SSRS.

SQL Server Reporting Services

Searching Reporting Services is only necessary if you have it installed, running, and have reports stored in it. If you do, then searching SSRS is only necessary on the report server itself. As a result, you may or may not need this TSQL, but it’s there if you do, and in our final search proc it will be configured as an option so that on systems without SSRS, no extra work is done.

Everything we access when searching SSRS will be found specifically within the ReportServer database that is built when Reporting Services is first configured. If you named it anything besides ReportServer, then substitute the correct name in its place. We’ll search for two report-related objects: reports and subscriptions. If a report itself has a name, path, definition, or settings that match the search criteria, then results will be returned for those reports. If a subscription exists that has a description or settings that match the search string, then info on those subscriptions will be returned.

Our work is limited to two tables, the first of which is Catalog, which provides information about every SSRS object defined on this report server. This table includes a variety of information besides reports, such as SSRS folders, resources, datasets, and data sources. The following query will return information on reports, as well as those additional objects:

Here, we search for our search string within the report path, name, definition, as well as the settings and description of any subscriptions associated with that report. The case statement checks each possible type and returns a friendly name for it. The results will look like this:

This shows a single folder matching our search criteria, as well as a single report.

Our second SSRS search query will only check subscriptions, and will return a row for any where the definition or settings match our search string. Since only subscriptions are searched, there is no need for additional joins or CASE statements to make sense of catalog types:

The results of this query show a single subscription matching the criteria provided:

Some additional details are included so that we know the subscription target, status, and details of its definition. Further querying can be done, if needed, to get even more info on the subscription, such as if any reports are using it, and details on each of them.

SQL Server Integration Services

Searching SSIS packages involves checking those defined within MSDB, which is similar to many of our previous searches, as well as searching those stored on disk, which requires xp_cmdshell or Powershell in order to search.

Our first search will be the simpler of the two, pulling data from MSDB and returning a list of SSIS packages, along with the details in both XML and as text:

The results show any SSIS packages defined within MSDB:

Here, we found a lone maintenance plan defined on my local server. For the object type, we explicitly label it as an SSIS package that was read from MSDB, which will help when locating it later on (if anything needs to be done with it).

Last, but not least, we have the chore of searching any SSIS packages that are located on disk. For this demo, we’ll use xp_cmdshell to search them, but Powershell or a file search utility could be used instead, if needed. We need to define up front the folder in which SSIS packages reside. As with SSRS, if you are not using SSIS, there is no need to search for SSIS packages. Here is TSQL that will check a given folder on disk for SSIS packages in XML format and search those definitions for our search terms:

There’s no candy-coating this one. We need to go out to disk and parse each XML file within the predefined SSIS package folder. Once we have returned the directory listing of each file in the folder, we’ll iterate through each one, bulk loading the data and storing the XML definition for the SSIS package.

Once we have this data in our temp table, the remainder of our work is the same as it was earlier. We update the text version of the package details so that we have a non-XML-typed string to run a text search against, and then return our data set, filtering out for our search criteria. Running this on my local server returns the following:

The results returned are exactly the same as in our previous SSIS search demo, except that we have explicitly defined the object type as “SSIS Package (file system), in order to differentiate it with any defined within MSDB.

In the event that any SSIS packages are encrypted within MSDB, then there is no simple way to search them using the framework that we have defined. In the spirit of keeping things as simple as possible, we’ll ignore these for this article at least, and perhaps revisit them in the future (with a vengeance).

Conclusion

In this article, we introduced a wide variety of system views and demonstrated how each can be used in order to return information about our SQL Server, both for the server as a whole as well as metadata specific to each individual database.

In my next article, we will bring everything from here together into a single script that can search all of the components discussed thus far within a single execution. The resulting script will be a tool that you can copy, tweak, customize, and deploy to any server where searching is needed!


Ed Pollack
Search

About Ed Pollack

Ed has 20 years of experience in database and systems administration, developing a passion for performance optimization, database design, and making things go faster. He has spoken at many SQL Saturdays, 24 Hours of PASS, and PASS Summit. This lead him to organize SQL Saturday Albany, which has become an annual event for New York’s Capital Region. In his free time, Ed enjoys video games, sci-fi & fantasy, traveling, and being as big of a geek as his friends will tolerate. View all posts by Ed Pollack

168 Views