TSQL 'smells' finders
TSQL Code can smell, it may work just fine but there can be hidden dangers held within.
This is a proof of concept work which will analyze TSQL scripts in an attempt to weed out some of these dangers.important
The powershell script utilizes a .NET 4 component so you will require Powershell 3, though Power gui works just fine too.
The script accepts several arguments :
$FolderPath Folderpath to recursively scan, defaults to script location
$FileSpec FileSpec to scan eg '*.sql,*.pql': Defaults to '*.sql'
$ConfigFolder FolderOf the configuration files "Errors.txt" and "Whitelist.txt"
$ExcludeErrors Exclude Specific Error numbers
$SupressGUI Supress GUI
$MinSeverity Only show errors higher than specified number, Errors.txt specifies a severity for each error , defaults to 9999999
The errornumbers are listed in the file errors.txt
The script can detect:
Avoid cross server joins
Use two part naming
Use of nolock / UNCOMMITTED READS
Use of Table / Query hints
Use of Select *
Explicit Conversion of Columnar data - Non Sargable predicates
Ordinal positions in ORDER BY Clauses
Change Of DateFormat
Change Of DateFirst
Missing Column specifications on insert
SET OPTION usage
Use 2 part naming in EXECUTE statements
Use of RANGE windows in SQL Server 2012
Create table statements should specify schema
View created with ORDER
SET NOCOUNT ON should be included inside stored procedures
COUNT(*) used when EXISTS/NOT EXISTS can be more performant
use of TOP(100) percent or TOP(>9999) in a derived table
Feedback is via a very simple GUI at the moment, the orignal smells.xml is still created.
Testcases are also included with this release
Please give me feedback to : email@example.com
If you like the concept but not the execution then i now have a version using an SSDT addin (not opensourced (yet ?!) , details herehttp://dataidol.com/davebally/2014/01/28/tsql-smells-in-ssdt-the-monster-lives/