question

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: - [CLR Assembly RegEx Functions for SQL Server by Example][1] by @Phil Factor - [SQL Server Regular Expressions and pattern matching][2] by @Pavel Pawlowski 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 [1]: https://www.red-gate.com/simple-talk/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/ [2]: https://pawlowski.cz/2010/09/12/sql-server-2005-and-sql-server-2008-regular-expressions-and-pattern-matching-2/