By: Jeffrey Yao | Updated: 2022-02-25 | Comments (13) | Related: > 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".
-- 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
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.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-02-25
