Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (7)   |   Related Tips: 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 anything like a string starting with 'XYZ'?

Solution

This is a very generic problem, and there are already lots of T-SQL solutions, such as this one. 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 (for SQL Server 2005 and later versions), we can have the following solution:

-- Applicable for SQL 2005+
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 such a 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

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 basic solution design is as follows:

  • Define and create a table to store the result
  • With SQLPS, 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+
-- 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 SQLPS
import-module sqlps -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.


Last Update:






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
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, January 31, 2015 - 11:23:03 PM - Thayal Muhunthan Back To Top

Microsoft  Provides

sp_stored_procedures (Transact-SQL) to search specific string such as sp_stored_procedures '%xyz%'


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

@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

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

Brilliant, Thank you


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

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

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


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

 

Its awaysome, very usefull for me


Learn more about SQL Server tools