detecting-t-sql-code-smells

detecting-t-sql-code-smells-1.ps1

$instanceName = "!instanceName"
$databaseName = "!databaseName"

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

function ScriptObject($subject, $scripter)
{
   if ($subject.IsSystemObject -eq $false)
   {
      $sc = $scrp.Script($subject.Urn)
   }
}

$srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$db = $srv.Databases.Item($databaseName)

#used for temporary storage
$filename = [System.IO.Path]::GetTempFileName()

#configure the scripter to write to the temp file
$scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)
$scrp.Options.AppendToFile = $true
$scrp.Options.FileName = $filename

#iterate through objects containing T-SQL code
foreach ($o in $db.StoredProcedures)
{
   ScriptObject $o $scrp
}

foreach ($o in $db.Tables)
{
   foreach ($ot in $o.Triggers)
   {
      ScriptObject $ot $scrp
   }
}

foreach ($o in $db.UserDefinedFunctions)
{
   ScriptObject $o $scrp
}

detecting-t-sql-code-smells-2.ps1

Get-Content $filename

detecting-t-sql-code-smells-3.ps1

...
	if($Stmt.OptimizerHints -ne $null){
		Process-OptimizerHints $Stmt.OptimizerHints
	}
...
...
function Process-OptimizerHints($OptimizerHints){
	foreach($Hint in $OptimizerHints){
		if(@("OrderGroup","MergeJoin","HashJoin","LoopJoin","ConcatUnion","HashUnion","MergeUnion","KeepUnion",
			"HashGroup","forceorder") -contains $Hint.HintKind){
			Send-FeedBack 4 $Hint
		}
	}
}
...

detecting-t-sql-code-smells-4.ps1

...
if($Script:WhiteListFiles -eq $null){
	Write-Host "Could not find file $ConfigFolder WhiteListFiles.txt. Whitelist will be ignored" -BackgroundColor Red -ForegroundColor white
} 
...

detecting-t-sql-code-smells-5.sql

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN    UPDATE Production.Product       SET ListPrice = ListPrice * 2    SELECT MAX(ListPrice) FROM Production.Product    IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
      BREAK
   ELSE
      CONTINUE
END

EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
    WAITFOR TIME '22:20';
    EXECUTE sp_update_job @job_name = 'TestJob',
        @new_name = 'UpdatedJob';
END;

detecting-t-sql-code-smells-6.ps1

...
	$StmtType = $Stmt.ToString().Split(".")[-1]

    if($StmtType -eq "WhileStatement"){
		Send-FeedBack 1001 $Stmt
	}
    if($StmtType -eq "WaitForStatement"){
		Send-FeedBack 1002 $Stmt
	}

	if($StmtType -eq "DeclareCursorStatement"){ 
...