SA0061B : Check table names used in CREATE TABLE statements for table name following specified naming convention

Introduction

Inconsistent or unclear table naming conventions can lead to confusion and difficulties in maintenance.

Description

Proper table naming is crucial in T-SQL and SQL Server environments. Using inconsistent or unclear names for tables can cause issues in understanding the schema and making future changes.

For example:

CREATE TABLE tblCustomerDetails (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100)
);

This table uses a prefix tbl, which might not follow a clear naming pattern.

Inconsistent naming makes it difficult to identify and relate different tables.
Ambiguous table names can complicate queries and maintenance tasks.

How to fix

Ensure tables are named consistently and clearly by adhering to a standard naming convention.

Follow these steps to address the issue:

1.Review existing table names and compare them with your organization’s naming convention. Confirm they are descriptive and easy to understand.

2.If a table name doesn’t meet the standard, rename it using the sp_rename stored procedure. Ensure no negative impact on dependencies.

3.Update any scripts, stored procedures, or application code that references the renamed table to ensure continuous operation.

For example:

-- Rename table to match naming convention
EXEC sp_rename 'tblCustomerDetails', 'CustomerDetails';

Scope

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

Parameters

Name	Description	Default Value
NamePattern	Table name pattern.	regexp:[A-Z][A-Za-z1-9_]+
SchemaQualifiedNamePattern	Schema qualified name pattern.	-
TemporaryTableNamePattern	Temporary table name pattern.	regexp:[A-Z][A-Za-z1-9_]+

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

8 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TABLE dbo.EmployeePhoto
    (
    EmployeeId int NOT NULL PRIMARY KEY
    ,Photo varbinary(max) FILESTREAM NULL
    ,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
        UNIQUE DEFAULT NEWID()
    );


CREATE TABLE [T1.T2]
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);

CREATE TABLE tblMyTable
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);

Analysis Results

	Message	Line	Column
1	SA0061B : The table [T1.T2] does not match the naming convention. The expected key name is [[A-Z][A-Za-z1-9_]+].	10	13
2	SA0061B : The table [tblMyTable] does not match the naming convention. The expected key name is [[A-Z][A-Za-z1-9_]+].	14	13

Analysis Rules