EX0009 : Consider adding proper comment block before each database object create statement

Introduction

The rule detects missing or incomplete comment headers in T-SQL scripts.

Description

It’s important to include comprehensive comment headers for stored procedures , triggers , views , and functions . These headers typically contain metadata like the author, creation date, and description, which can be crucial for maintenance and collaboration in SQL Server environments.

Example of T-SQL stored procedure with a header:

-- =============================================
-- Author:             Author's name
-- Create Date:        2024-05-23
-- Description:        Example Stored Procedure
-- Update Date:        2025-01-04
-- =============================================
CREATE PROCEDURE MyProcedure
AS
BEGIN
    SELECT * FROM MyTable;
END;

This script is missing a comment header, which may lead to:

Lack of critical metadata, making it difficult to track the history and purpose of the SQL component.
Challenges in collaborating, as other developers may not understand the context or intent without detailed documentation.

The rule parameters provide standard regular expression templates for matching the specific comment block elements (Author, Create Date and etc.).

How to fix

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
AuthorLineTemplate	Regular expression to match the Author line in the header block.	Author\s:\s\w+
CreatedDateLineTemplate	Regular expression to match the Create Date line in the header block.	Create Date\s*:.
UpdatedDateLineTemplate	Regular expression to match the Update Date line in the header block.	Update Date\s*:.
UpdatedByLineTemplate	Regular expression to match the Update By line in the header block.	Update By\s:\s\w+
DescriptionLiteTemplate	Regular expression to match the Description line in the header block.	Description\s*:.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

5 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- =============================================
-- Author:    Author's name
-- Create Date: 2010-05-01
-- Description:  Example Stored Procedure
-- Update Date:  2010-05-19
-- =============================================
CREATE PROCEDURE MyProcedureName


  @p1 int = 0,
  @p2 int = 0
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

    -- Insert statements for procedure here
  SELECT 1, @p2
END

Analysis Results

	Message	Line	Column
1	EX0009 : The create statement for procedure [MyProcedureName] is missing Update By line in its header.	1	0

Analysis Rules