x

Find the stored procedures which has table variable on it

i want to find the stored procedures,Just procedure name. which has table variable on it

more ▼

asked Feb 08 at 12:08 PM in Default

avatar image

thennarasu
31 2 6

What do you mean by "have table variable on it"? As a parameter to the procedure or is there a reference to a table variable anywhere in the procedure code?

Feb 08 at 01:13 PM Magnus Ahlkvist

a particular table variable? Or any?

Either way, it might be worth a quick look at redgate's SQL Search, a free SSMS plug-in. https://www.red-gate.com/products/sql-development/sql-search/

Feb 08 at 04:11 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

In order to find the complete list of the stored procedures which use table variables somewhere inside of the procedure text, it is necessary to build the search pattern first. Unfortunately, the SQL Server built-in functionality does not include the regular expressions patterns matching, and therefore, the task becomes not trivial. Let's say that every developer who creates procedures in your company always uses the same pattern to declare table variables with only one space between the words declare, variable name, and table. Something like this:

 declare @someTableVar_01 table -- etc,

In this case, you can simply use the query like this:

 select
     o.[object_id] ProcID, schema_name(o.[schema_id]) + '.' + o.[name] ProcName
     from sys.objects o inner join sys.sql_modules m
         on o.[object_id] = m.[object_id]
     where o.[type] = N'P' and m.[definition] like '%declare @% table%';

The query will find the list of the procedures provided that the declaration of the table variables is consistent. Unfortunately, this will never be the case, and the script will fail to find the procedures which use a slightly different pattern to declare table variables, say with a line break after declare, the tab before the variable name and "as table" with the tab between as and table. Something like this:

 declare 
     @t as        table -- etc

The only clean way to reliably find the list of all procedures is via regular expressions pattern matching. As I already mentioned, this functionality is not available in SQL Server out of the box, and you will have to implement it yourself. For example, you can use one of the available CLR assemblies. Any one of the following two will enable RegEx functionality in your SQL Server instance:

Alternatively, in case if the SQL Server version in question is 2016 or higher with machine learning services (in -database) already installed and configured, you might want to consider using R script. Here is the solution which uses R script (in SQL Server 2017 you may use Python if it is more convenient, I stick with R for now because it is really awesome addition to the existing SQL Server functionality):

 -- R script to find and return the list of procedures which use table variables
 declare @script nvarchar(max) = '
 ## search pattern to find the procedures using table variables
 pattern <- "declare\\s+@\\w+\\s*(as)?\\s+table"
 
 ## find matching rows and pick just the first 2 columns (ID and Name) to reduce traffic
 output <- subset(input, grepl(pattern, ProcText, ignore.case = T))[, c("ProcID", "ProcName")]
 ';
 
 -- SQL script used as an input dataset for R script
 declare @sql nvarchar(max) = '
 select
     o.[object_id] ProcID, schema_name(o.[schema_id]) + ''.'' + o.[name] ProcName,
     m.[definition] ProcText
     from sys.objects o inner join sys.sql_modules m
         on o.[object_id] = m.[object_id]
     where o.[type] = N''P''
 ';
 
 exec sp_execute_external_script 
     @language = N'R', @script = @script, @input_data_1 = @sql, 
     @input_data_1_name = N'input', @output_data_1_name = N'output'
     with result sets ((ProcID int not null, ProcName varchar(128) not null)); 

The pattern used in the script: "declare\\s+@\\w+\\s*(as)?\\s+table" may be described like this: the word declare followed by one-or-more occurrence of any white-space characters (tabs, line breaks, spaces) followed by @ character followed by one or more of any word characters (numbers, letters and/or underscores) followed by one-or-more occurrence of any white-space characters followed by optional as followed by one-or-more occurrence of any white-space characters followed by the word table. With this pattern, the R script will find all procedures which have the table variables in procedure definitions regardless of how the declarations are spelled out.

Hope this helps.

Oleg

more ▼

answered Feb 14 at 08:21 AM

avatar image

Oleg
19.8k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2207
x2034
x411
x141

asked: Feb 08 at 12:08 PM

Seen: 60 times

Last Updated: 4 days ago

Copyright 2018 Redgate Software. Privacy Policy