,

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.
How to create a simple analysis rule with SQL Enlight that checks for new tables created without a CreationDate column
,

CR0003: Non-ISO standard comparison operator found

Message

Non-ISO standard comparison operator found

Description

It is advisable to use ISO standard comparison operators instead of non-ISO standard operators to help ensure optimal cross-platform and future version compatibility.

  • Not equal to: Use <> instead of !=
  • Greater than or equal to: Use >= instead of !<
  • Less than or equal to: Use <= instead of !>

While it is currently acceptable to use such non-ISO operators, you should consider that statements that you create might not be supported on other ISO-compliant database management systems.

Also, non-ISO standard comparison operators may not be supported on future versions of SQL Server.

Author

Phil Streiff

Example

-- Test Case 1: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 != 1
-- Test Case 2: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 !< 1
-- Test Case 3: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 !> 1

-- Test Case 4: A violation should not be reported
SELECT Column1 FROM Table1 WHERE Column1 <> 1
-- Test Case 5: A violation should not be reported
SELECT Column1 FROM Table1 WHERE Column1 >= 1
-- Test Case 6: A violation should no be reported
SELECT Column1 FROM Table1 WHERE Column1 <= 1

Download and try the CR0003 analysis rule.

Download

,

CR0002: Avoid altering security within stored procedures

Message

Avoid altering security within stored procedures

Description

The rule checks and alerts for usage of GRANT, REVOKE, or DENY statements within the body of a stored procedure.
Avoid altering security within stored procedures, functions, and triggers. This can lead to unnecessary database calls, or it can hinder troubleshooting security permissions.

Author

Jeff Foushee

Example

CREATE PROCEDURE testsp_CR0002 (
    @Code VARCHAR(30) = NULL
)
AS

BEGIN
    IF @Code IS NULL
        SELECT * FROM Table1
    ELSE
        SELECT * FROM Table1 WHERE Code like @Code + '%'

    UPDATE MyTable SET Col1 = 'myvalue'

    BEGIN TRAN
        GRANT EXEC ON testsp_CR0002 to myuser
    COMMIT TRAN

    GRANT EXEC ON testsp_CR0002 to myuser  --IGNORE:CR0002

    REVOKE SELECT ON dbo.Table1 TO myuser

    DENY EXECUTE ON testsp_CR0002 to myuser

END

-- this is fine because it is outside of the stored procedure
GRANT EXEC ON testsp_CR0002 to myuser  

Download and try the CR0002 analysis rule.

Download
,

CR0001: TOP (100) PERCENT

Message

TOP (100) PERCENT found

Description

This rule checks for the phrase “TOP (100) PERCENT”.
This phrase has no bearing unless the percentage is less than 100.
This phrase is commonly generated by creating a view in the SQL Server View Designer.

Author

Jeff Foushee

Example

SELECT TOP 100 PERCENT
LastName, FirstName, JobTitle, Department
FROM       HumanResources.vEmployeeDepartment
ORDER BY LastName ASC

 

Download and try the CR0001 analysis rule.

Download

List of the new analysis rules in version 1.9.0.587

These are the new rules added in the analysis template of SQL Enlight 1.9.0.587:

  1. SA0060 : The sp_xml_preparedocument procedure call is not paired with a following sp_xml_removedocument call.
  2. SA0061A : Check all Tables in the current database for following specified naming convention.(Context Only)
  3. SA0061B : Check table names used in CREATE TABLE statements for table name following specified naming convention.(Batch)
  4. SA0062A : Check all Functions in the current database for following specified naming convention.(Context Only)
  5. SA0062B : Check function names used in CREATE FUNCTION statements for following specified naming convention. (Batch)
  6. SA0063A : Check all Views in the current database for following specified naming convention.(Context Only)
  7. SA0063B : Check view names used in CREATE VIEW statements for following specified naming convention. (Batch)
  8. SA0064A : Check all Stored Procedures in the current database for following specified naming convention.(Context Only)
  9. SA0064B : Check stored procedure names used in CREATE PROCEDURE statements for following specified naming convention. (Batch)
  10. SA0065A : Check all Triggers for for following specified naming convention.(Context Only)
  11. SA0065B : Check trigger names used in CREATE TRIGGER statements for for following specified naming convention. (Batch)
  12. SA0066A : Check all Columns for following specified naming convention.(Context Only)
  13. SA0067A : Check all Unique Key Constraints in the current database for following specified naming convention.(Context Only)
  14. SA0068A : Check all Check Constraints in the current database for following specified naming convention.(Context Only)
  15. SA0069A : Check all Default Constraints in the current database for following specified naming convention.(Context Only)
  16. SA0070A : Check all Primary Key Constraints in the current database for following specified naming convention.(Context Only)
  17. SA0071A : Check all Foreign Key Constraints in the current database for following specified naming convention.(Context Only)
  18. SA0072A : Check all Non-Key Indexes in the current database for following specified naming convention.(Context Only)
  19. SA0073A : Check all User-Defined Types in the current database for following specified naming convention.(Context Only)
  20. SA0074A : Check all Schemas in the current database for following specified naming convention.(Context Only)
  21. SA0075 : Avoid constraints created with system generated name.
  22. SA0076 : Check UPDATE and DELETE statements for not filtering using all the PRIMAR KEY columns of the target table.
  23. SA0077 : Avoid executing dynamic code using EXECUTE statement.
  24. SA0078 : Statement is not terminated with semicolon.
  25. SA0079 : Avoid using column numbers in ORDER BY clause.
  26. SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length.
  27. SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale.
  28. SA0082 : Consider prefixing column names with table name or table alias.
  29. SA0085 : Check database objects for missing specific extended properties.