SA0073B : Check all User-Defined Types for following specified naming convention

Introduction

Inconsistent or unclear user-defined types naming can lead to confusion and maintenance challenges.

Description

In T-SQL scripts, user-defined types are created using the CREATE TYPE statement. A major problem arises when the naming conventions for these types are inconsistent or unclear, which can lead to confusion, increased error rates, and difficulties in database management.

For example:

-- Poor naming practice for user-defined type
CREATE TYPE abc123 AS TABLE (...);

This example uses a non-descriptive name that does not convey the type’s purpose or content, complicating schema reviews and future maintenance efforts.

Unclear names make it difficult for developers and administrators to understand the database’s schema, especially in large or complex systems.
Inconsistent naming can lead to increased cognitive load and errors during development, as team members may misinterpret or misuse types.

How to fix

Ensure consistent and meaningful naming for user-defined types in SQL to improve maintainability and clarity.

Follow these steps to address the issue:

1.Review the existing object name in the CREATE TYPE statement.

2.Refer to your organization’s naming conventions for database objects to identify the appropriate format and structure for the type name.

3.Rename the type using a descriptive and consistent name that clearly indicates its purpose and content.

4.Update any database scripts or application code that reference the renamed type to ensure consistency in the database schema.

For example:

-- Original poorly named user-defined type
CREATE TYPE abc123 AS TABLE (
    Column1 INT,
    Column2 VARCHAR(50)
);

-- Corrected with a meaningful name following naming conventions
CREATE TYPE OrderItemTableType AS TABLE (
    ItemID INT,
    ItemDescription VARCHAR(50)
);

Scope

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

Parameters

Name	Description	Default Value
NamePattern	Deault constraint name pattern.	regexp:[A-Z][A-Za-z]+

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 TYPE SSN
FROM varchar(11) NOT NULL ;

CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string] ;

CREATE TYPE LocationTableType AS TABLE
    ( LocationName VARCHAR(50)
    , CostRate INT );

Analysis Results

	Message	Line	Column
1	SA0073B : The user defined type name Utf8String does not match the naming convention. The expected name is [[A-Z][A-Za-z]+].	4	12

Analysis Rules