Project Description
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
SET ROWCOUNT
Missing Column specifications on insert
SET OPTION usage
Use 2 part naming in EXECUTE statements
SET IDENTITY_INSERT
Use of RANGE windows in SQL Server 2012
Create table statements should specify schema
View created with ORDER
Writable cursors
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 : parser@clearskysql.co.uk

If you like the concept but not the execution then i now have a version using an SSDT addin (not opensourced (yet ?!) , details here
http://dataidol.com/davebally/2014/01/28/tsql-smells-in-ssdt-the-monster-lives/

Last edited Feb 6 at 9:51 AM by daveballantyne, version 11