Find Table Variables definitions in Stored Procedures

Question

As part of tuning of a large database, I am first of all interested in finding all the table variables.

I am using this script:

SELECT [Schema]=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 '%DECLARE @%TABLE%'

It returns many results but most of them are irrelevant.

How can I change the search pattern to match an exact definition?

Answer 1

The LIKE function is not designed to handle the level of complexity that is necessary for this type of string pattern. The % matches 0 or more of anything. Hence, DECLARE @%TABLE matches any code that has "DECLARE @" + anything + "TABLE", where anything can be 1000 lines, or 10 MB worth, of code. Or even just a space. For this you really need Regular Expressions (i.e. RegEx). However, there is no built-in RegEx functionality in SQL Server. However, as long as you are using SQL Server 2005 or newer (and not using Azure SQL Database [not including Managed Instances] or SQL Server 2017 on Amazon RDS), then you can get RegEx functionality via SQLCLR. You can either code these yourself (and there are plenty of examples floating around), or you can download and install SQL#, a SQLCLR library (that I wrote) that has a Free version which includes several RegEx functions.

One thing to keep in mind is that table variables can be declared using a user-defined table type (UDTT) as the definition. In such cases you would not look for the string TABLE but the name of the UDTT. And, to complicate things, UDTTs can be specified with an optional schema name followed by a period. Oh, and, the UDTT name and/or optional schema name may or may not be delimited by square brackets (we will ignore, for the moment, using double-quotes as identifier delimiters). Oh, AND, if the optional schema name is specified, there can be white-space on either side of the period separator (fo' reelz, yo!). And let us not forget that @, #, and $ are also valid characters to use in identifiers. Fun stuff!!!

The following pattern should cover the vast majority of situations (not 100% of possibilities, but highly unlikely that someone would be doing something outside of what this will match ^** ):

DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)

The example below uses the SQL# function, RegEx_Matches, and the pattern shown above to find table variable declared either directly as tables or by using a user-defined table type:

-- DROP TYPE dbo.Test_Table;
IF (TYPE_ID(N'dbo.Test_Table') IS NULL)
BEGIN
  CREATE TYPE dbo.[Test_Table] AS TABLE ([ID] INT);
END;

DECLARE @SQL NVARCHAR(MAX) = N'
Declare @a dbo.Test_Table;
Declare
@b dbo.[Test_Table];

Declare @c [dbo].Test_Table;
Declare @d [dbo ].[Test_Table];
Declare @e [dbo]
    .
        [Test_Table];

Declare             @f Test_Table;
Declare @g
[Test_Table];
Declare @h [Test_Table  ]; -- space(s) at the end, not tab(s)

DeclarE @T1     TABLE(col1 int);
DeclarE @T_2 TABLE
(col2 int);
DeclarE
    @$#@        TABLE (
    col3 int);
';

EXEC (@SQL); -- make sure that T-SQL is valid

SELECT * FROM SQL#.RegEx_Matches(@SQL,
N'DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)',
                                 1, N'IgnoreCase');

That SELECT returns the following results:

MatchNum   Value                                StartPos   EndPos   Length
1          Declare @a dbo.Test_Table            3          27       25

2          Declare
           @b dbo.[Test_Table]                  31         58       28

3          Declare @c [dbo].Test_Table          64         90       27

4          Declare @d [dbo ].[Test_Table]       94         123      30

5          Declare @e [dbo]
            .
                [Test_Table]                    127        162      36

6          Declare              @f Test_Table   168        191      24

7          Declare @g
           [Test_Table]                         195        218      24

8          Declare @h [Test_Table  ]            222        246      25

9          DeclarE @T1      TABLE(              287        305      19

10         DeclarE @T_2 TABLE
           (                                    318        338      21

11         DeclarE
            @$#@        TABLE (                 351        373      23

You should be able to find everything in your code by doing something along the lines of:

SELECT *
FROM   sys.sql_modules mdl
CROSS APPLY SQL#.RegEx_Matches(mdl.[definition],
  N'DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)',
                               1, N'IgnoreCase') tvar;

---

^** Well, ok, this pattern doesn't account for comments placed between the "DECLARE" and variable name, or between the variable name and "TABLE" or UDTT name. But, that is a whole other level of complication that is, for the moment at least, maybe not best handled inside SQL Server, in which case something outside of SQL Server would be your best option. Something like TSqlParser, as Martin suggested in a comment on the question, or maybe SMO can handle this.

DECLARE       -- This
    @Valid    -- is
  /* valid
       /* DECLARE @NotReal TABLE([Col1] TINYINT);
    */  
  */  TABLE   -- T-SQL
    ( [col1]  INT );