SA0074A : Check all Schema-s in the current database for following specified naming convention

In SQL Server, having consistent and descriptive schema names enhances clarity and maintenance. The main problem is inconsistent naming across schemas, leading to confusion and making it difficult to understand database structures or enforce security measures. A lack of naming standards can also create challenges in identifying relationships between different schemas and tables, impacting both development and database administration tasks.

For example:

1-- Example of inconsistent schema naming
2SELECT * FROM SalesData.TableName;
3SELECT * FROM HRData.TableName;

This example illustrates schemas that may not follow a standardized naming pattern. Such inconsistency can obscure the purpose or content of the schemas, affect database readability, and disrupt automated processes relying on naming conventions.

• Inconsistent schema names decrease database readability.

• It complicates maintenance and security policy enforcement.

Ensure consistent and descriptive schema naming conventions to improve database readability and maintainability.

Follow these steps to address the issue:

1. Analyze current schema names and identify any inconsistencies or deviations from your organization’s naming conventions.

2. Develop or refer to a standardized schema naming convention that reflects the purpose or content of each schema. For example, use prefixes or suffixes to denote specific applications or departments, like Sales_ or HR_.

3. Rename schemas to comply with the established naming convention. Use ALTER SCHEMA … TRANSFER … to move objects to the new schema when necessary.

4. Update any dependent scripts, stored procedures, or applications to reference the new schema names.

5. Test thoroughly to ensure that all references and functionalities are correctly aligned with the revised schema names.

For example:

1-- Example of corrected schema naming convention
2ALTER SCHEMA Sales RENAME TO SalesData;
3ALTER SCHEMA HR RENAME TO HRData;
4-- Updating a table to the new schema
5ALTER SCHEMA SalesData TRANSFER dbo.TableName;

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

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

Naming Rules, Code Smells

There is no additional info for this rule.

Other Resources