2

It is clear that omitting the length from a varchar is a bad thing. Unfortunately I am now working with a code base where this has happened. Extensively. I would like to correct this. The first step will be to find the occurrences. This is where I need help.

Searches on various web engines using all the synonyms I can think of return no authoritative answer. I'm asking for

  • additional test cases which I've missed
  • a comprehensive, canonical way of finding declarations without a length

Any technology which is typically available on a Windows development environment (SSMS, Powershell, .Net etc.) is good. Answers employing more niche technologies would be interesting for the wider community but less so for me personally.

Tests

Since the four data types in question - char, nchar, varchar and nvarchar - all end with the characters C-H-A-R I use this alone in the tests below. This saves bloating the list and makes adding further tests simpler. It'll be easy enough to copy-paste-replace should the need arise.

-- These are all legal; the regex must not return these
char(9)
char (9)            -- with a space
char    (9)         -- with a tab
char         (9)    -- tab space tab space
char(max)
char
(9)                 -- a new line between type and length

character(9)
CAST(999 AS character(9))

char varying(9)
character varying(9)
CAST(999 AS char varying(9))
CAST(999 AS character varying(9))


-- These also are legal; ugly, but legal
[char](9)
[char] (9)          -- with a space
[char]  (9)         -- with a tab
[char]       (9)    -- tab space tab space
[char](max)
[char]
(9)                 -- a new line between type and length

-- The type can also be delimited by double-quote
"char"(9)
-- All the tests using square brackets should be duplicated with other delimiters.

[character](9)
CAST(999 AS [character](9))

-- SQL Server 2022 throws an error for [character varying]
-- Msg 243, Level 16, State 1, Line 15
-- Type character varying is not a defined system type.


-- These are business terms which the regex should not return
characteristic
charge
chart

-- These are valid SQL but missing the length. These are what the search should return
char;
char ;      -- a space
char    ;   -- a tab
char,
char ,
char = 'lorem'
cast(9 as char)
convert(char, 9)

[char];
[char] ;        -- a space
[char]  ;   -- a tab
[char],
[char] ,
[char] = 'lorem'
cast(9 as [char])
convert([char], 9)

character
CAST(999 AS character)

char varying
character varying
CAST(999 AS char varying)
CAST(999 AS character varying)

5
  • @Akina what I mean is that the four characters C-H-A-R are also the last four characters of the string VARCHAR and of the string NVARCHAR. This question is not about finding bytes in mdf files. It's about finding T-SQL in stored procedure definitions. Jul 12, 2023 at 13:00
  • I'm asking for additional test cases which I've missed Linebreak between datatype and length is allowed too. Also test non-breaking space (0xA0)..
    – Akina
    Jul 12, 2023 at 13:00
  • In theory, you probably can reduce / remove most of your individual test cases, and have a more complete check, if you instead use RegEx to do a negated forward lookahead for ( after char and char]. In other words, return any instances where ( doesn't exist anywhere in the remaining string. In practice, I'm not sure how possible it would be to do with just SSMS or T-SQL and may need to be done in code that has full RegEx support. This isn't perfect either, as a variable could be instantiated to its own string containing '(' but the RegEx could be evolved to account for that too.
    – J.D.
    Jul 12, 2023 at 20:09
  • Ah commented before I reviewed your answer. Seems like you're on the right track already.
    – J.D.
    Jul 12, 2023 at 21:23
  • char(max) is not legal. It expects an integer expression
    – Zikato
    Jul 18, 2023 at 10:48

3 Answers 3

6

Regex is not the correct way to go about this. There will always be false positives that are impossible/extremely hard to find. For example, multiline comment blocks

Instead, I recommend using the SqlScriptDOM, which is a .NET library for parsing T-SQL statements and interacting with its abstract syntax tree provided by Microsoft.

Then you can use a .NET application or PowerShell to identify the missing char size exactly.

You could iterate over your code base in source control or sys.sql_modules and pass the contents to the ScriptDOM function.

I borrowed most of the code from Dan Guzman's blog

# this is the script to parse
$script = @"
-- These are all legal; the regex must not return these

declare @test char(20)

SET @test = CAST (@test AS char     (15 )) /* CHAR( 15 ) is intentional */
DECLARE @test2 char
(
    100
)

RAISERROR ('Char(max) is not valid',16 ,1) WITH nowait
SELECT CHAR(64) AS EmailSeparator -- this char(64) returns @

CREATE TABLE #UglyLegal
(
    a [char](9)
    , b [char] (9)          -- with a space
    , c [char]  (9)         -- with a tab
    , d [char]       (9)    -- tab space tab space
    , e [char]
(9)   
    , characteristic int
    , charge bit
    , chart bit
)


-- These are errors that will be returned
declare @a char;
GO
declare @a char ;      -- a space
GO
declare @a char    ;   -- a tab
GO
declare @a char, @b bit
GO
declare @a char , @b bit
GO
declare @a char = 'lorem'
GO
SELECT cast(9 as char)
GO
SELECT CONVERT(char, 9)

CREATE TABLE #CharFamily
(
    a char(10)
    , b char
    , c nchar(10)
    , d nchar
    , e varchar(MAX)
    , f varchar
    , g nvarchar(10)
    , h nvarchar
)

/* these examples are courtesy of Paul White https://sql.kiwi/ */
DECLARE @foo char varying = 'aaron';
SELECT foo = @foo, lenfoo = LEN(@foo);
SELECT LEN(CAST(REPLICATE('a', 255) AS char varying));
GO
DECLARE @foo "varchar" = 'aaron';
SELECT foo = @foo, lenfoo = LEN(@foo);
SELECT LEN(CAST(REPLICATE('a', 255) AS "varchar"));
"@

try {

    class MyVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlConcreteFragmentVisitor {

        [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.SqlDataTypeReference] $fragment) {
            # Write-Host "$($fragment.SqlDataTypeOption) - size $(($fragment.Parameters[0]).Value) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow

            if (!($fragment.Parameters[0].Value) -and $fragment.SqlDataTypeOption -in ('Char', 'NChar', 'VarChar', 'NVarChar')) {
                Write-Host "Data type $($fragment.SqlDataTypeOption) is missing size at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Red
            }
        }

    }
    
    # Create trusted NuGet package source, if needed
    $packageSource = Get-PackageSource | Where-Object { ($_.Location -EQ "https://www.nuget.org/api/v2") -and ($_.ProviderName -eq "NuGet") -and ($_.IsTrusted -eq $true) }
    if ($packageSource -eq $null) {
        Register-PackageSource NuGetV2 https://www.nuget.org/api/v2 -ProviderName NuGet -Trusted
    }

    # Install package, if needed.
    $tSqlScriptDomPackage = Install-Package Microsoft.SqlServer.TransactSql.ScriptDom -Source ($packageSource.Name) -Scope CurrentUser
    # Get package
    $tSqlScriptDomPackage = Get-Package -Name Microsoft.SqlServer.TransactSql.ScriptDom

    # Load Microsoft.SqlServer.TransactSql.ScriptDom.dll .NET framework assembly into app domain for use in PS scripts
    $tSqlScriptDomPackageFolderPath = [System.IO.Path]::GetDirectoryName($tSqlScriptDomPackage.Source)
    Add-Type -LiteralPath "$tSqlScriptDomPackageFolderPath\lib\net462\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true) # Find the correct compatibility level https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#compatibility_level--160--150--140--130--120--110--100--90--80- */

    # create an ParseError collection for any errors returned by parser
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]

    # create a StringReader for the script for parsing
    $stringReader = New-Object System.IO.StringReader($script)

    # parse the script
    $tSqlFragment = $parser.Parse($stringReader, [ref]$parseErrors)

    # raise an exception if any parsing errors occur
    if ($parseErrors.Count -gt 0) {
        throw "$($parseErrors.Count) parsing error(s): $(($parseErrors | ConvertTo-Json))"
    }
        
    $visitor = [MyVisitor]::new()
    $tSqlFragment.Accept($visitor)

}
catch {
    throw
} 

Here I'm using the TSql150Parser, which matches SQL Server 2019 according to the Compatibility level table

This would be the output

Data type Char is missing size at line 29, column 12, length 4
Data type Char is missing size at line 31, column 12, length 4
Data type Char is missing size at line 33, column 12, length 4
Data type Char is missing size at line 35, column 12, length 4
Data type Char is missing size at line 37, column 12, length 4
Data type Char is missing size at line 39, column 12, length 4
Data type Char is missing size at line 41, column 18, length 4
Data type Char is missing size at line 43, column 16, length 4
Data type Char is missing size at line 48, column 9, length 4
Data type NChar is missing size at line 50, column 9, length 5
Data type VarChar is missing size at line 52, column 9, length 7
Data type NVarChar is missing size at line 54, column 9, length 8
Data type VarChar is missing size at line 58, column 14, length 12
Data type VarChar is missing size at line 60, column 40, length 12
Data type VarChar is missing size at line 62, column 14, length 9
Data type VarChar is missing size at line 64, column 40, length 9
3
  • 1
    It would be helpful to see an object or file name in the output when iterating over many inputs via files or management views. Jul 18, 2023 at 15:23
  • 2
    Yes, you can add whatever info is useful, this is not meant as a complete solution.
    – Zikato
    Jul 18, 2023 at 16:17
  • We have a lot of dynamic SQL which this misses. It does eliminate commented-out code which is helpful. Sep 28, 2023 at 13:25
1

SQL Server Management Studio (SSMS) allows the use of regex in searches. My skills in this are minimal. What I have is this

  char[^agt\(]]?\s*(?!(\s|\(|\]))

char is the data type in question, and the suffix to varchar & nvarchar.

[^agt\(] prevents matching on the business terms listed above. I've only included the three letters needed for my current corpus. In a different application these terms could be expanded or omitted entirely. I had to include the left-parenthesis (escaped as \() otherwise char(.. would have been matched.

]? allows for zero or one right square bracket.

\s* allows for zero, one or many whitespace characters.

(?!) is a negative lookahead. It means "not followed by." I really just want a left-paren here but had to create an or-list (( | | )) including whitespace (\s) and right-square (\]) to get the desired matches.

This resulted mostly from trial and error. Greedy/ lazy evaluation especially threw me off. I'd love to have improvement suggestions.

0

Test this:

\bn?(var)?char[]]?\s*\(\d+\)

https://regex101.com/r/bTLmR9/3

PS. Errors should be checked separately.

1
  • This finds those examples which correctly have a length. I'm asking to find the ones missing a length. I like the way it starts on a word boundry, though. Jul 18, 2023 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.