How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

By:   |   Updated: 2022-02-25   |   Comments (12)   |   Related: More > PowerShell


Problem

How can I accurately find which SQL Server Stored Procedures, Views or Functions are using a specific text string, which can be a table name or any string that is part of the code?

Solution

This is a very common problem and there are already lots of T-SQL solutions, such as this article How to Find Keywords in SQL Server Stored Procedures and Functions. However, there are some short-comings to the generic solution.

Let's use an example, say I want to find any Stored Procedure, View or Function that contains a table called tblABC. Using the generic approach (works for SQL Server 2005 and later versions), we can have the following solution:

-- Applicable for SQL 2005 and later versions
USE [Your_DB];
GO

SELECT [Scehma] = schema_name(o.schema_id), o.Name, o.type 
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition like '%tblABC%'
GO

Two obvious issues with this solution are:

  • If you have a table called tblABC123, the return will include objects containing tblABC123 instead of tblABC only.
  • If tblABC is included in comments, the return will include objects containing such comments

A Better Solution to Search T-SQL Code

To address the issues mentioned above, a better solution can be as follows:

  • If we remove all comments and then split each word of the Stored Procedure, Trigger, View or Function and save the words in a table, we can then easily find any <text_string> of our interest with a SELECT statement.

Actually, this is a little bit of an over-kill solution to the original question, but this solution, besides eliminating the above issues, may provide convenient insights into some other interesting questions, such as whether there is any INSERT, UPDATE or DELTE commands in a Stored Procedure, how many times a table is used in a Stored Procedure, are there any temp tables used, etc.

The solution design is as follows:

  • Define and create a table to store the result
  • With PowerShell we can loop through all Stored Procedures, Views or Functions of a database, and with .net RegEx class, we can filter out comments and then split each word in the text body of each object
  • Dump the result of step 2 into the table of step 1

Here is the source code:

1. Create a SQL Server table to store the results:

-- Applicable for SQL 2005 and later versions
-- run in SSMS window
USE [TempDB]; -- change to your own database
GO

CREATE TABLE dbo.tblWord (
    id int identity primary key
  , DBName varchar(80)
  , ObjectName varchar(80)
  , ObjectType varchar(10)
  , Word varchar(80)
  , [Count] int
)
GO

2. PowerShell script to split Stored Procedure code.  To check views, UDFs or triggers just change the $type in the script below.

#Requires -Version 3.0
#Requires -Modules sqlserver
import-module sqlserver -DisableNameChecking;
 
function MatchEval-Function # as a delegate function
{ param ([string]$x)
   
  if ($x.StartsWith('/*') -or $x.StartsWith('--'))
  { return "";}
  else {return $x;}
}
[string]$ServerName ='TP_W520' #change to your sql server machine name 
 
[string]$database = 'AdventureWorks2012'; # change to your own db name
[string]$type = 'sp' # 'sp' | 'vw' | 'udf' | 'trg';
 
#any words in $filter_words will not be counted 
[string[]] $filter_words='begin', 'end'; #you can add whatever you prefer to filter out

[string]$object=''

switch ($type)
{
   'vw' {$object='Views'; break;}
   'sp' {$object='StoredProcedures'; break;}
   'udf' {$object='UserDefinedFunctions'; break;}
   'trg' {$object='Triggers'; break;}
}
 
$rslt = New-Object System.Data.DataTable;
$col = New-Object System.Data.DataColumn('DBName', ([String]))
$rslt.columns.Add($col);

$col = New-Object System.Data.DataColumn('ObjectName', ([String]))
$rslt.columns.Add($col);
$col = New-Object System.Data.DataColumn('ObjectType', ([String]))
$rslt.columns.Add($col);
$col = New-Object System.Data.DataColumn('Word', ([String]))
$rslt.columns.Add($col);
$col = New-Object System.Data.DataColumn('Count', ([Int]))
$rslt.columns.Add($col);

# $Reg_exp_1 is to find all regular comments if the comment is inside quotation marks, just ignore
[string]$reg_exp_1 = @'
["'][^'"]*?['"]|(?m)--.*$|(?s)/\*.*?\*/[\r\n]?
'@;
 
$RegEx= new-object "System.Text.RegularExpressions.RegEx" ($reg_exp_1);
 
[string]$reg_exp_2 = '(?m)^[;\s]*$[\r\n]'; #remove empty lines
$RegEx2= new-object "System.Text.RegularExpressions.RegEx" ($reg_exp_2);
 
##this is to replace all multiple blank spaces, character '=' and '.' as ONE blank space
[string]$reg_replace = '\t{1,}|\r\n|\s{2,}|=|\.|,|\(|\)';
$RegEx_Replace = new-object "System.Text.RegularExpressions.RegEx" ($reg_replace);
 
#these chars will be removed ;+[]'
[string]$reg_remove = @'
\(\d+\)|;|'|\+|\[|]
'@
 
$RegEx_Remove = new-object "System.Text.RegularExpressions.RegEx" ($reg_remove)
 
#if your sql instance is not the default one, you need to change "default" to your sql instance name
dir sqlserver:\sql\$ServerName\default\databases\$database\$object |
% {
    $o = $_;
    $s = $_.textheader + $_.textbody;
    $ss = $RegEx.replace($s, {MatchEval-Function $args[0].value}); #remove all comments
    $ss = $RegEx2.replace($ss, ''); #remove all empty lines;

    $ss = $RegEx_Remove.replace($ss, '');
 
    $ss = $RegEx_replace.replace($ss, ' ');
 
 
    $ss.split(' ') | where {($_.length -gt 0) -and ($_ -notin $filter_words) }  |
    select @{l='ObjectName'; e={$o.Schema+'.'+$o.name}}, @{l='Word'; e={$_}}  |
    Group-Object -Property ObjectName, Word -NoElement |
    % { $r = $rslt.NewRow(); 
        $r.DBName = $database;
        $r.ObjectName=($_.Name -split ', ')[0]; 
        $r.ObjectType = $Type;
        $r.Word=($_.Name -split ', ')[1];
        $r.Count = $_.Count;
        $rslt.Rows.add($r);
      }
}

#we need to prepare to write the collected info into a central place. 
$col_mapping = @{};
$col_mapping.Add('DBName','DBName'); # in the format of (sourceColumn, destinationColumn)

$col_mapping.Add('ObjectName','ObjectName');
$col_mapping.Add('ObjectType','ObjectType');
$col_mapping.Add('Word','Word');
$col_mapping.Add('Count','Count');
 
#need to replace TempDB to your own environment settings
#insert the splited word into the pre-defined table
$conn = New-Object System.Data.SqlClient.SqlConnection ("Server=$ServerName; Database=TempDB; trusted_connection=TRUE");
$conn.Open();
$bulkcopy = New-Object System.Data.SqlClient.SqlBulkCopy($conn);
 
$col_mapping.keys | % {$bc_mapping = new-object System.Data.SqlClient.SqlBulkCopyColumnMapping($_, $col_mapping[$_]); $bulkcopy.ColumnMappings.Add($bc_mapping); } | Out-Null;
$bulkcopy.DestinationTableName='dbo.tblWord'; #you may change to your own table
$bulkcopy.WriteToServer($rslt);

3. We can check which Stored Procedures are using which tables:

USE AdventureWorks2012;
GO

SELECT w.ObjectName, [TableName] = t.name, w.[Count] 
FROM sys.tables t
INNER JOIN tempdb.dbo.tblWord w ON t.name = w.word;
GO

Note: The [Count] column may have a bigger number than it actually should. For example, for table Person.Person, i.e. schema and table names are both called "Person", the [Count] value will be doubled for word "Person".

Tables used by SPs

-- Check all empty tables that are used in the SPs
USE AdventureWorks2012;
GO

SELECT w.ObjectName, [TableName] = t.name, [RowCount] = p.Rows 
FROM sys.tables t
INNER JOIN tempdb.dbo.tblWord w ON t.name = w.word
INNER JOIN sys.partitions p on p.object_id = t.object_id and p.rows = 0 and p.index_id < 2
GO

EmptyTable_in_SP
Next Steps
  • Open SQL Server Management Studio and PowerShell ISE, copy and paste the corresponding scripts, and modify $ServerName/$Database values to your own.
  • You can modify the script to automatically scan all Stored Procedures, Triggers, Views or Functions on all user databases on all servers, and dump the data into an central repository table. With such information, you may come up with various creative usages, one possible use is that you can audit whether there are changes to any Stored Procedure, Trigger, View or Function after an implementation.
  • With tiny changes, we can expand this analysis to SQL Server Agent Jobs because there may be a case you need to change a table or view name, you need to know whether this change will impact any SQL Server Agent Jobs too.
  • Read this related interesting article Auto Rename SQL Server Tables and Their References with PowerShell.





get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips


Article Last Updated: 2022-02-25

Comments For This Article




Tuesday, March 29, 2022 - 9:33:14 AM - Greg Robidoux Back To Top (89946)
Hi Jeff, thanks for the input. We can look into this to see if we can add the date. The reason we didn't have the first published date is because of how the search engines grab content and the dates they show on search results. If you look at the web page source it does show the original publish date and last update date.

-Greg

Tuesday, March 29, 2022 - 9:23:44 AM - Jeff Moden Back To Top (89945)
I wish that this site would add a "First Published" date right next to the "Last Updated" date for articles. Heh... I was going to chew on Jeffrey a bit (his articles are usually impeccable) for resorting to PowerShell for something that has been easy to do in SQL Server using the built-in STRING_SPLIT() function since 2016. I learned my lesson on that quite a while ago, though. I always look at the earliest comment to try to figure closer to the original publish date.

If I may... Jeffrey, please consider adding that method as a separate article for this common task and adding a link to the beginning of this article for folks that have 2016 and above. Like I said, this is a very common task and the built in string splitter would make this task a cake walk in T-SQL. Someone could even turn it into a system stored procedure in master and simply call it with one or more words to lookup as a CSV parameter. It would be a wonderful update to this great article.

Friday, May 17, 2019 - 9:29:28 AM - Jon Back To Top (80114)

The powershell code is returning an error for me.

Missing statement after '=' in hash literal.
At line:78 char:76

I have not worked in powershell before, so I do not know what is missing.

Thanks,


Wednesday, April 18, 2018 - 2:11:04 PM - jeff_yao Back To Top (75730)

 

@Ray, totally agree. Actually, I will talk about these two DMFs in one of my future tips.

But the method I mentioned in this tip is more generic and is not only for referenced objects, it can answer some fun questions such as how many UPDATE statements in a stored procedure? 

Thanks for your comments.

 


Wednesday, April 18, 2018 - 1:36:20 PM - Ray Back To Top (75729)

 You should study the DMFs sys.dm_sql_referenced_entities  and sys.dm_sql_referencing_entities which are available from at least SQL2008R2 and later.

These DMFs provide forward and backward links (e.g., who do I touch and who touches me).

I changed my text search scripts to use the DMFs and I have found they meet all my needs.

 


Friday, February 2, 2018 - 9:23:20 AM - Brenda Back To Top (75088)

so are block comments ( /* something */), line comments (--somthing) and embedded comments ( /* /* something */ */ ) all removed?

 


Friday, January 30, 2015 - 12:47:38 PM - jeff_yao Back To Top (36121)

@shaimakem, no, you cannot copy & paste into SSMS to run the PS script. You need to run the script in PowerShell IDE. 


Friday, January 30, 2015 - 7:48:32 AM - shaimakem Back To Top (36114)

I'm not familiar with PowerShell scripting or SQLPS, is it safe for me to just copy and paste the code above into SSMS to benifit from the output result? only changing the server name and the DB name? thanks


Wednesday, January 28, 2015 - 4:56:53 AM - BurtD Back To Top (36082)

Brilliant, Thank you


Tuesday, January 27, 2015 - 4:41:34 PM - Jeff Back To Top (36075)

SQL Search by redgate is an excellent free tool for finding strings.


Tuesday, January 27, 2015 - 3:04:03 PM - jeff_yao Back To Top (36074)

Thanks Mahmood for the feedback, I am glad it helps.


Tuesday, January 27, 2015 - 7:20:42 AM - Mahmood Ali Back To Top (36067)

 

Its awaysome, very usefull for me















get free sql tips
agree to terms