A new project, incomplete documentation, and the original author gone. I need to know “what updates and uses this object?”. So, how do I search for it?.

If there is good source control and code management, a simple search will work. In many cases, there isn’t the luxury of code management.

Search the code behind procedures, views and other objects

I have cooked up these short scripts to help with the search through through objects. The code which makes up views and stored procedures is accessible through some sys views. This allows us to find what uses a table or other object, or even look for a magic number.

Both scripts dynamically create an SQL statement, which searches through the object definitions. Note, this type of dynamic SQL is not ideal for production code for security reasons. As this isn’t a production procedure, this is a good time to use dynamic SQL.

Single database (catalogue)

Most of the time, we are only interested in a single database/catalogue in SQL server. The below allows you to change the value for @search_text to anything you’d like to search for.

V1

-------------------------------------------------------------------------------
-- Current DB Catalogue
-------------------------------------------------------------------------------
DECLARE @search_text  VARCHAR(200);
SET @search_text = '%reporting_entites%';
SELECT o.[object_id],
       db_name()      AS [db_catalog_name],
       o.[name]       AS [Object_Name],
       m.[definition],
       o.[type_desc],
       s.[name]       AS [schema_name],
       '[' + s.[name] + '].[' + o.[name] + ']' AS [formatted_Name]
  FROM sys.objects o
     LEFT JOIN sys.sql_modules m ON m.[object_id] = o.[object_id]
     LEFT JOIN sys.schemas     s ON o.[schema_id] = s.[schema_id]
 WHERE(LOWER(m.definition) LIKE LOWER(@search_text)
    OR LOWER(o.name)       LIKE LOWER(@search_text)
      )
;

V2

Allowing for quotes in the string and dynamically adding the percentages

All databases (catalogues)

This extends the single database code to iterate through multiple databases/catalogues in SQL server. The below allows you to change the value for @search_text to anything you’d like to search for.

For this I included an error list, this mostly covers cases where the user in question doesn’t have access to view the metadata tables in the database. Often this will be system databases, or on a large system databases which are relevant to other teams/departments and probably won’t contain anything we as the user are interested in anyway. Note without the try/catch the execution could fail and not cover all databases.