SA0073A : Check all User-Defined Types in the current database for following specified naming convention

Introduction

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

Description

When working with T-SQL in SQL Server, it is essential to establish naming conventions for user-defined types to maintain consistency and readability. Poor naming can lead to confusion for database administrators and developers, especially in large or complex databases.

For example:

-- Example of a poorly named user-defined type
CREATE TYPE My_UType AS TABLE (column1 INT);

The above example shows a user-defined type with a non-standard name. Inconsistent naming can result in difficulty managing and understanding schema components, especially when debugging or integrating with other systems.

Inconsistent naming conventions complicate code readability and maintenance.
Lack of standardized names can lead to errors or misunderstandings during database operations or collaboration.

How to fix

Ensure user-defined types in SQL Server use consistent naming conventions for clarity and maintainability.

Follow these steps to address the issue:

1.Identify user-defined types with non-standard or inconsistent naming in your database. You can use the sys.types catalog view to list types:

2.Analyze the names to determine if they adhere to your organization’s naming conventions. Common conventions might include prefixes or suffixes that indicate the type’s purpose or scope.

3.If a user-defined type’s name does not follow the convention, use the ALTER TYPE statement to change it, or drop and recreate the type with an appropriate name:

For example:

-- Dropping and recreating a user-defined type with a standardized name
DROP TYPE IF EXISTS My_UType;
GO
CREATE TYPE AccountType AS TABLE (AccountId INT);

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Name	Description	Default Value
NamePattern	User defined data type name pattern.	regexp:[A-Z][A-Za-z]+

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

8 minutes per issue.

Additional Information

There is no additional info for this rule.

Analysis Rules