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.
