Searching for String Literals in T-SQL Code

If you have ever worked in a multi-language (i.e. localization-aware) project, you know that you should avoid string literals embedded in code.

To make sure that no untranslatable error messages, warnings or other text that might end up in the user interface is embedded in T-SQL code (MS SQL Server stored procedures, functions, and triggers), you’d need to search each of the code blocks for the string delimiter ‘ (single quote).

I’ll sketch a solution called tsqlfindstrings implemented in PowerShell using SMO to access the code stored in a MS SQL Server database, and .Net regular expressions to find string literals. The script tsqlfindstrings.ps1 needs to be configured similarly to my previous PowerShell solution automssqlbackup.

First, the SQL Server-specific SMO libraries are loaded

$mspath = "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\"
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.ConnectionInfo.dll")
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.Smo.dll")
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.SmoExtended.dll")

and connection parameters $dbhost, $dbname, $username, and $password are set.

Next, we define string literals to be excluded from the search results, either complete strings or string beginnings, as string arrays:

$excludedStrings = @(" (", ")", "%", "*", " - ", ", ", "(", "true", "false", " ", """", "=""",
  "uniqueidentifier", "datetime", "int", "decimal", "bit", "float", "Z",  "string", "boolean", "guid")
$excludedBegins = @("'")

The example shows T-SQL keywords as excluded strings and the single quote as first character in a string literal (both used for dynamic SQL, so we want to ignore these).

We connect using the standard SMO objects ServerConnection and Server:

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
[void] $srv.Initialize($false) 
[void] $srv.Refresh() 
[void] $srv.SetDefaultInitFields($true)

to find our database $db in $srv.Databases.

In its current version, the script checks these SMO properties for string literals:

  • Tables.Checks.Text
  • Tables.Columns.Default
  • Views.TextBody
  • StoredProcedures.TextBody
  • UserDefinedFunctions.TextBody
  • Triggers.TextBody

If a single quote is found in the property values, the Extract subroutine is called to extract the string literal and match it with the defined excluded strings:

foreach($m in [RegEx]::Matches($s, "'(.+?)'[^']") |
  Where-Object { ($excludedStrings -notcontains $_.Groups[1]) })
  $g = $m.Groups[1].Value
  $do = $true
  foreach($b in $excludedBegins)
    if ($g.StartsWith( $b ))
      $do = $false

  if ($do)
    Write-Host ($type + " " + $name + ": '" + $g + "'")

The first released version of tsqlfindstrings is available for download here.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: