SA0074B : Check all Schema-s for following specified naming convention

Introduction

Inconsistent or unclear database schema naming can lead to confusion and maintenance challenges.

Description

In SQL Server, the correct naming of schemas is crucial for maintaining a clear and consistent database structure. Incorrect or inconsistent schema names can lead to confusion, difficulty in managing database objects, and potential security issues.

For example:

-- Example of a query with potentially problematic schema naming
CREATE SCHEMA Incorrect_SchemaName123;

This example might be problematic because it uses a non-standard naming convention that does not align with organizational or project-specific naming standards. This can make it difficult to manage and understand the database structure.

Inconsistent naming conventions can lead to confusion when querying or managing the database, especially in large and complex systems.
Non-standard names may not accurately reflect the purpose of the schema, leading to misunderstandings among team members.

How to fix

Ensure schema names in CREATE SCHEMA statements align with organizational naming conventions for clarity and consistency.

Follow these steps to address the issue:

1.Review the current schema name used in the CREATE SCHEMA statement and compare it with the established naming conventions within your organization or project.

2.If the schema name does not comply, adjust it to adhere to your naming standards. Ensure the name is descriptive and reflective of the schema’s purpose.

3.Update any references to the schema name in the database queries, stored procedures, or other database objects to maintain consistency and avoid potential errors.

4.Test the changes in a development environment to verify that all references to the renamed schema are correctly updated and no errors occur due to the name change.

For example:

-- Example of a corrected CREATE SCHEMA statement
CREATE SCHEMA CorrectSchemaName;

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 SCHEMA Sprockets AUTHORIZATION Annik
    CREATE TABLE NineProngs (source int, cost int, partnumber int)
    GRANT SELECT ON SCHEMA::Sprockets TO Mandar
    DENY SELECT ON SCHEMA::Sprockets TO Prasanna;

ALTER SCHEMA Sprockets TRANSFER Person.Address;

Analysis Results

No violations found.

Analysis Rules