How to: Create a T-SQL code analysis rule reporting tables created without a required column

This is an example of creating a custom analysis rule with SQL Enlight that checks T-SQL code for CREATE TABLE statements that do not have a required column in the table definition.

In our example, the required column will be CreationDate and these are the steps for creating the rule:

1. Open SQL Server Management Studio
2. Prepare a new script or find an existing script that represents the expected code and one that violates the rule. In our case, we have two tables – table1 created with the CreationDate column and table2 created without the required column.
3.  Select the CreationDate from the first table and use the editor’s context menu to select SQL Enlight -> Create Simple Analysis rule menu item.
4. The analysis rule designer is opened with a test script and a match expression preset to the XPath expression matching the CreatoinDate column.
5. Go to the Properties tab and set the rule name, message, and description fields.
6. Go to Match Expression, make sure that the CreationDate column is selected, and click the Add Expression button.
7. The initial match expression matches the CreationDate column inside CREATE TABLE statements:
   $batch-statements[sqml:is('CreateTable', .)]
/k:create/g:brackets/g:commalist/i:common
[@name = 'CreationDate'][sqml:is('Column', .)][pr:datatype[@name = 'datetime']][k:not/k:null]


   The expression has to be adjusted to fulfill the rule requirements:

   • • Match the table name instead of the CreationDate column:
       $batch-statements[sqml:is('CreateTable', .)]
/k:create
[ g:brackets/g:commalist/i:common [@name = 'CreationDate'][sqml:is('Column', .)][pr:datatype[@name = 'datetime']][k:not/k:null] ]/*[self::i:* or self::pu:dot]

     • Inverse the check of the existence of a CreationDate column and match CREATE TABLE statements that do not have such a column:
       $batch-statements[sqml:is('CreateTable', .)]
/k:create
[ g:brackets/g:commalist
[ not( i:common[@name = 'CreationDate'][sqml:is('Column', .)][pr:datatype[@name = 'datetime']][k:not/k:null] )]
]/*[self::i:* or self::pu:dot]

     • Update the Match custom message to reflect the current analysis rule.
8. Test the rule expression and verify that the expected table is reported by the rule
9. Saving the created analysis rule will make it available in the current analysis rule template.