Searching SQL Server made easy - Searching catalog views

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:

SELECT

databases.name AS database_name,

'Database' AS object_type

FROM sys.databases

WHERE databases.name LIKE '%Adventure%';

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:

SELECT

syslogins.name AS login_name,

'Server Login' AS object_type

FROM sys.syslogins

WHERE syslogins.name LIKE '%edward%';

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):

SELECT

sysservers.srvname AS server_name,

sysjobs.name AS objectname,

sysjobs.description AS object_description,

sysjobs.enabled,

'SQL Agent Job'

FROM msdb.dbo.sysjobs

INNER JOIN master.dbo.sysservers

ON srvid = originating_server_id

WHERE sysjobs.name LIKE '%verification%'

OR sysjobs.description LIKE '%verification%';

SELECT

s.srvname AS server_name,

sysjobs.name AS objectname,

sysjobsteps.step_name,

sysjobs.description AS object_description,

sysjobsteps.command AS object_definition,

sysjobs.enabled,

'SQL Agent Job Step'

FROM msdb.dbo.sysjobs

INNER JOIN msdb.dbo.sysjobsteps

ON sysjobsteps.job_id = sysjobs.job_id

INNER JOIN master.dbo.sysservers s

ON s.srvid = sysjobs.originating_server_id

WHERE sysjobsteps.command LIKE '%verification%'

OR sysjobsteps.step_name LIKE '%verification%';

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:

SELECT

servers.name AS server_name,

servers.data_source,

'Linked Server' AS object_type

FROM sys.servers

WHERE servers.name LIKE '%2016%'

OR servers.data_source LIKE '%2016%';

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:

SELECT

db_name() AS database_name,

tables.name AS table_name,

'Table' AS object_type

FROM sys.tables

WHERE tables.name LIKE '%Person%';

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:

SELECT

db_name() AS database_name,

tables.name AS table_name,

columns.name AS column_name,

'Column' AS object_type

FROM sys.tables

INNER JOIN sys.columns

ON tables.object_id = columns.object_id

WHERE columns.name LIKE '%BusinessEntityID%';

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:

SELECT

db_name() AS database_name,

schemas.name AS schema_name,

tables.name AS table_name,

columns.name AS column_name,

'Column' AS object_type

FROM sys.tables

INNER JOIN sys.columns

ON tables.object_id = columns.object_id

INNER JOIN sys.schemas

ON schemas.schema_id = tables.schema_id

WHERE columns.name LIKE '%BusinessEntityID%';

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:

SELECT

db_name() AS database_name,

schemas.name AS schema_name

FROM sys.schemas

WHERE schemas.name LIKE '%Person%';

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:

SELECT

db_name() AS database_name,

synonyms.name AS synonym_name,

synonyms.base_object_name,

'Synonym' AS object_type

FROM sys.synonyms

WHERE synonyms.name LIKE '%product%'

OR synonyms.base_object_name LIKE '%product%';

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:

SELECT

db_name() AS database_name,

tables.name AS table_name,

indexes.name AS index_name,

'Index' AS object_type

FROM sys.indexes

INNER JOIN sys.tables

ON tables.object_id = indexes.object_id

WHERE indexes.name LIKE '%ProductCategory%';

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!

WITH CTE_INDEX_COLUMNS AS (

SELECT

db_name() AS database_name,

TABLE_DATA.name AS table_name,

INDEX_DATA.name AS index_name,

STUFF(( SELECT ', ' + columns.name

FROM sys.tables

INNER JOIN sys.indexes

ON tables.object_id = indexes.object_id

INNER JOIN sys.index_columns

ON indexes.object_id = index_columns.object_id

AND indexes.index_id = index_columns.index_id

INNER JOIN sys.columns

ON tables.object_id = columns.object_id

AND index_columns.column_id = columns.column_id

WHERE INDEX_DATA.object_id = indexes.object_id

AND INDEX_DATA.index_id = indexes.index_id

AND index_columns.is_included_column = 0

ORDER BY index_columns.key_ordinal

FOR XML PATH('')), 1, 2, '') AS key_column_list,

STUFF(( SELECT ', ' + columns.name

FROM sys.tables

INNER JOIN sys.indexes

ON tables.object_id = indexes.object_id

INNER JOIN sys.index_columns

ON indexes.object_id = index_columns.object_id

AND indexes.index_id = index_columns.index_id

INNER JOIN sys.columns

ON tables.object_id = columns.object_id

AND index_columns.column_id = columns.column_id

WHERE INDEX_DATA.object_id = indexes.object_id

AND INDEX_DATA.index_id = indexes.index_id

AND index_columns.is_included_column = 1

ORDER BY index_columns.key_ordinal

FOR XML PATH('')), 1, 2, '') AS include_column_list,

'Index Column' AS object_type

FROM sys.indexes INDEX_DATA

INNER JOIN sys.tables TABLE_DATA

ON TABLE_DATA.object_id = INDEX_DATA.object_id)

SELECT

database_name,

table_name,

index_name,

key_column_list,

ISNULL(include_column_list, '') AS include_column_list

FROM CTE_INDEX_COLUMNS

WHERE CTE_INDEX_COLUMNS.key_column_list LIKE '%PurchaseOrderID%'

OR CTE_INDEX_COLUMNS.include_column_list LIKE '%PurchaseOrderID%';

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:

SELECT

name,

'Queue' AS object_type

FROM sys.service_queues

WHERE service_queues.name LIKE '%Test_Queue%';

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:

SELECT

schemas.name AS schema_name,

objects.name AS parent_table,

foreign_keys.name AS foreign_key_name,

'Foreign Key' AS object_type

FROM sys.foreign_keys

INNER JOIN sys.schemas

ON foreign_keys.schema_id = schemas.schema_id

INNER JOIN sys.objects

ON objects.object_id = foreign_keys.parent_object_id

WHERE foreign_keys.name LIKE '%Customer%';

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:

WITH CTE_FOREIGN_KEY_COLUMNS AS (

SELECT

parent_schema.name AS parent_schema,

parent_table.name AS parent_table,

referenced_schema.name AS referenced_schema,

referenced_table.name AS referenced_table,

foreign_keys.name AS foreign_key_name,

STUFF(( SELECT ', ' + referencing_column.name

FROM sys.foreign_key_columns

INNER JOIN sys.objects

ON objects.object_id = foreign_key_columns.constraint_object_id

INNER JOIN sys.tables parent_table

ON foreign_key_columns.parent_object_id = parent_table.object_id

INNER JOIN sys.schemas parent_schema

ON parent_schema.schema_id = parent_table.schema_id

INNER JOIN sys.columns referencing_column

ON foreign_key_columns.parent_object_id = referencing_column.object_id

AND foreign_key_columns.parent_column_id = referencing_column.column_id

INNER JOIN sys.columns referenced_column

ON foreign_key_columns.referenced_object_id = referenced_column.object_id

AND foreign_key_columns.referenced_column_id = referenced_column.column_id

INNER JOIN sys.tables referenced_table

ON referenced_table.object_id = foreign_key_columns.referenced_object_id

INNER JOIN sys.schemas referenced_schema

ON referenced_schema.schema_id = referenced_table.schema_id

WHERE objects.object_id = foreign_keys.object_id

ORDER BY foreign_key_columns.constraint_column_id ASC

FOR XML PATH('')), 1, 2, '') AS foreign_key_column_list,

STUFF(( SELECT ', ' + referenced_column.name

FROM sys.foreign_key_columns

INNER JOIN sys.objects

ON objects.object_id = foreign_key_columns.constraint_object_id

INNER JOIN sys.tables parent_table

ON foreign_key_columns.parent_object_id = parent_table.object_id

INNER JOIN sys.schemas parent_schema

ON parent_schema.schema_id = parent_table.schema_id

INNER JOIN sys.columns referencing_column

ON foreign_key_columns.parent_object_id = referencing_column.object_id

AND foreign_key_columns.parent_column_id = referencing_column.column_id

INNER JOIN sys.columns referenced_column

ON foreign_key_columns.referenced_object_id = referenced_column.object_id

AND foreign_key_columns.referenced_column_id = referenced_column.column_id

INNER JOIN sys.tables referenced_table

ON referenced_table.object_id = foreign_key_columns.referenced_object_id

INNER JOIN sys.schemas referenced_schema

ON referenced_schema.schema_id = referenced_table.schema_id

WHERE objects.object_id = foreign_keys.object_id

ORDER BY foreign_key_columns.constraint_column_id ASC

FOR XML PATH('')), 1, 2, '') AS referenced_column_list,

'Foreign Key Column' AS object_type

FROM sys.foreign_keys

INNER JOIN sys.tables parent_table

ON foreign_keys.parent_object_id = parent_table.object_id

INNER JOIN sys.schemas parent_schema

ON parent_schema.schema_id = parent_table.schema_id

INNER JOIN sys.tables referenced_table

ON referenced_table.object_id = foreign_keys.referenced_object_id

INNER JOIN sys.schemas referenced_schema

ON referenced_schema.schema_id = referenced_table.schema_id)

SELECT

parent_schema,

parent_table,

referenced_schema,

referenced_table,

foreign_key_name,

foreign_key_column_list AS foreign_key_column_list,

referenced_column_list AS referenced_column_list

FROM CTE_FOREIGN_KEY_COLUMNS

WHERE CTE_FOREIGN_KEY_COLUMNS.foreign_key_column_list LIKE '%SpecialOfferID%'

OR CTE_FOREIGN_KEY_COLUMNS.referenced_column_list LIKE '%SpecialOfferID%';

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:

SELECT

default_constraints.name AS default_constraint_name,

schemas.name AS parent_schema_name,

objects.name AS parent_table_name,

columns.name AS parent_column_name,

default_constraints.definition AS default_definition,

'Default Constraint' AS object_type

FROM sys.default_constraints

INNER JOIN sys.objects

ON objects.object_id = default_constraints.parent_object_id

INNER JOIN sys.schemas

ON objects.schema_id = schemas.schema_id

INNER JOIN sys.columns

ON columns.object_id = objects.object_id

AND columns.column_id = default_constraints.parent_column_id

WHERE default_constraints.name LIKE '%Quantity%'

OR default_constraints.definition LIKE '%Quantity%';

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:

SELECT

check_constraints.name AS check_constraint_name,

schemas.name AS parent_schema_name,

objects.name AS parent_table_name,

check_constraints.definition AS check_definition,

'Check Constraint' AS object_type

FROM sys.check_constraints

INNER JOIN sys.objects

ON objects.object_id = check_constraints.parent_object_id

INNER JOIN sys.schemas

ON objects.schema_id = schemas.schema_id

WHERE check_constraints.name LIKE '%Discount%'

OR check_constraints.definition LIKE '%Discount%';

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:

SELECT

server_triggers.name AS trigger_name,

parent_class_desc AS trigger_type,

server_sql_modules.definition AS trigger_definition,

'Server Trigger' AS object_type

FROM sys.server_triggers

INNER JOIN sys.server_sql_modules

ON server_triggers.object_id = server_sql_modules.object_id

WHERE server_triggers.name LIKE '%Create%'

OR server_sql_modules.definition LIKE '%Create%';

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:

SELECT

triggers.name AS trigger_name,

triggers.parent_class_desc AS trigger_type,

sql_modules.definition AS trigger_definition,

'Database DDL Trigger' AS object_type

FROM sys.triggers

INNER JOIN sys.sql_modules

ON triggers.object_id = sys.sql_modules.object_id

WHERE parent_class_desc = 'DATABASE'

AND (triggers.name LIKE '%test%' OR sql_modules.definition LIKE '%test%');

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:

SELECT

child_object.name AS object_name,

parent_schema.name AS parent_schema_name,

parent_object.name AS parent_object_name,

sql_modules.definition AS object_definition,

CASE child_object.type

WHEN 'P' THEN 'Stored Procedure'

WHEN 'RF' THEN 'Replication Filter Procedure'

WHEN 'V' THEN 'View'

WHEN 'TR' THEN 'DML Trigger'

WHEN 'FN' THEN 'Scalar Function'

WHEN 'IF' THEN 'Inline Table Valued Function'

WHEN 'TF' THEN 'SQL Table Valued Function'

WHEN 'R' THEN 'Rule'

END AS object_type

FROM sys.sql_modules

INNER JOIN sys.objects child_object

ON sql_modules.object_id = child_object.object_id

LEFT JOIN sys.objects parent_object

ON parent_object.object_id = child_object.parent_object_id

LEFT JOIN sys.schemas parent_schema

ON parent_object.schema_id = parent_schema.schema_id

WHERE child_object.name LIKE '%Purchase%'

OR sql_modules.definition LIKE '%Purchase%'

ORDER BY child_object.type_desc

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:

SELECT

Catalog.Path AS SSRS_object_path,

Catalog.Name AS SSRS_object_name,

CONVERT(XML, CONVERT(VARBINARY(MAX), Catalog.content)) AS xml_definition,

CONVERT(NVARCHAR(MAX), CONVERT(XML, CONVERT(VARBINARY(MAX), Catalog.content))) AS text_definition,

CASE Catalog.Type

WHEN 1 THEN 'Folder'

WHEN 2 THEN 'Report'

WHEN 3 THEN 'Resource'

WHEN 4 THEN 'Linked Report'

WHEN 5 THEN 'Data Source'

WHEN 6 THEN 'Report Model'

WHEN 7 THEN 'Report Part'

WHEN 8 THEN 'Shared Dataset'

ELSE 'Unknown'

END AS SSRS_object_type

FROM reportserver.dbo.Catalog

LEFT JOIN ReportServer.dbo.Subscriptions

ON Subscriptions.Report_OID = Catalog.ItemID

WHERE Catalog.Path LIKE '%Test%'

OR Catalog.Name LIKE '%Test%'

OR CONVERT(NVARCHAR(MAX), CONVERT(XML, CONVERT(VARBINARY(MAX), Catalog.content))) LIKE '%Test%'

OR Subscriptions.DataSettings LIKE '%Test%'

OR Subscriptions.ExtensionSettings LIKE '%Test%'

OR Subscriptions.Description LIKE '%Test%';

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:

SELECT

Subscriptions.Description AS subscription_description,

Subscriptions.LastStatus AS subscription_status,

Subscriptions.DeliveryExtension AS subscription_delivery_method,

Subscriptions.ExtensionSettings AS subscription_details,

'Subscription' AS object_type

FROM ReportServer.dbo.Subscriptions

WHERE Subscriptions.ExtensionSettings LIKE '%Test%'

OR Subscriptions.Description LIKE '%Test%'

OR Subscriptions.DataSettings LIKE '%Test%';

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:

WITH CTE_SSIS AS (

SELECT

pf.foldername + '\'+ p.name AS full_path,

CONVERT(XML,CONVERT(VARBINARY(MAX),packagedata)) AS package_details_XML,

CONVERT(NVARCHAR(max), CONVERT(XML,CONVERT(VARBINARY(MAX),packagedata))) AS package_details_text,

'SSIS Package (MSDB)' AS object_type

FROM msdb.dbo.sysssispackages p

INNER JOIN msdb.dbo.sysssispackagefolders pf

ON p.folderid = pf.folderid)

SELECT

FROM CTE_SSIS

WHERE CTE_SSIS.package_details_text LIKE '%test%'

OR CTE_SSIS.full_path LIKE '%test%';

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:

CREATE TABLE ##ssis_data

( full_path NVARCHAR(MAX),

package_details_XML XML,

package_details_text NVARCHAR(MAX) );

DECLARE @pkg_directory NVARCHAR(MAX) = 'C:\SSIS_Packages';

DECLARE @ssis_package_name NVARCHAR(MAX);

DECLARE @sql_command VARCHAR(4000);

SELECT @sql_command = 'dir "' + @pkg_directory + '" /A-D /B /S ';

INSERT INTO ##ssis_data

(full_path)

EXEC Xp_cmdshell @sql_command;

DECLARE SSIS_CURSOR CURSOR FOR

SELECT full_path FROM ##ssis_data;

OPEN SSIS_CURSOR;

FETCH NEXT FROM SSIS_CURSOR INTO @ssis_package_name;

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @sql_command = 'WITH CTE_SSIS_PACKAGES AS (

SELECT

''' + @ssis_package_name + ''' AS full_path,

CONVERT(XML, SSIS_PACKAGE.bulkcolumn) AS package_details_XML

FROM OPENROWSET(BULK ''' + @ssis_package_name + ''', single_blob) AS SSIS_PACKAGE)

UPDATE SSIS_DATA

SET package_details_XML = CTE_SSIS_PACKAGES.package_details_XML

FROM CTE_SSIS_PACKAGES

INNER JOIN ##ssis_data SSIS_DATA

ON CTE_SSIS_PACKAGES.full_path = SSIS_DATA.full_path;'

FROM ##ssis_data;

EXEC (@sql_command);

FETCH NEXT FROM SSIS_CURSOR INTO @ssis_package_name;

END

CLOSE SSIS_CURSOR;

DEALLOCATE SSIS_CURSOR;

UPDATE SSIS_DATA

SET package_details_text = CONVERT(NVARCHAR(MAX), SSIS_DATA.package_details_XML)

FROM ##ssis_data SSIS_DATA;

SELECT

'SSIS Package (File System)' AS object_type

FROM ##ssis_data SSIS_DATA

WHERE SSIS_DATA.package_details_text LIKE '%test%'

OR SSIS_DATA.full_path LIKE '%test%';

DROP TABLE ##ssis_data;

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!

Author
Recent Posts

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

SQLShack

Searching SQL Server made easy – Searching catalog views

Background

The Building Blocks of a Search Solution

Database Names

Logins

Jobs

Linked Servers

Tables

Columns

Schemas

Synonyms

Indexes

Service Broker Queues

Constraints

DDL Triggers

Stored Procedures, Views, Functions, Rules, and Triggers

SQL Server Reporting Services

SQL Server Integration Services

Conclusion

Background

The Building Blocks of a Search Solution

Database Names

Logins

Jobs

Linked Servers

Tables

Columns

Schemas

Synonyms

Indexes

Service Broker Queues

Constraints

DDL Triggers

Stored Procedures, Views, Functions, Rules, and Triggers

SQL Server Reporting Services

SQL Server Integration Services

Conclusion

Related posts: