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

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:

SQL
Copy
 1-- =============================================
 2-- Author:             Author's name
 3-- Create Date:        2024-05-23
 4-- Description:        Example Stored Procedure
 5-- Update Date:        2025-01-04
 6-- =============================================
 7CREATE PROCEDURE MyProcedure
 8AS
 9BEGIN
10    SELECT * FROM MyTable;
11END;

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.).

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.

Authors*:s*w+
CreatedDateLineTemplate

Regular expression to match the Create Date line in the header block.

Create Dates*:.
UpdatedDateLineTemplate

Regular expression to match the Update Date line in the header block.

Update Dates*:.
UpdatedByLineTemplate

Regular expression to match the Update By line in the header block.

Update Bys*:s*w+
DescriptionLiteTemplate

Regular expression to match the Description line in the header block.

Descriptions*:.
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Explicit Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- =============================================
 2-- Author:                Author's name
 3-- Create Date: 2010-05-01
 4-- Description:        Example Stored Procedure
 5-- Update Date:        2010-05-19
 6-- =============================================
 7CREATE PROCEDURE MyProcedureName 
 8
 9
10        @p1 int = 0, 
11        @p2 int = 0
12AS
13BEGIN
14        -- SET NOCOUNT ON added to prevent extra result sets from
15        -- interfering with SELECT statements.
16        SET NOCOUNT ON;
17
18    -- Insert statements for procedure here
19        SELECT 1, @p2
20END
Analysis Results
  Message Line Column
1 EX0009 : The create statement for procedure [MyProcedureName] is missing Update By line in its header. 1 0
See Also

Other Resources

Analysis Rules