i want to find the stored procedures,Just procedure name. which has table variable on it
asked Feb 08 at 12:08 PM in Default
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:
In this case, you can simply use the query like this:
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:
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):
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.