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

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:

SQL
Copy
1-- Example of a query with potentially problematic schema naming
2CREATE 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:

SQL
Copy
1-- Example of a corrected CREATE SCHEMA statement
2CREATE 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.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
1CREATE SCHEMA Sprockets AUTHORIZATION Annik  
2    CREATE TABLE NineProngs (source int, cost int, partnumber int)  
3    GRANT SELECT ON SCHEMA::Sprockets TO Mandar  
4    DENY SELECT ON SCHEMA::Sprockets TO Prasanna;  
5
6ALTER SCHEMA Sprockets TRANSFER Person.Address;
Analysis Results

No violations found.

See Also

Other Resources

Analysis Rules