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 );