SA0063B : Check view names used in CREATE VIEW statements for following specified naming convention

Use consistent and descriptive naming conventions for views to ensure clarity and maintainability.

Description

Naming conventions for views in T-SQL are crucial for maintaining readability and manageability of SQL Server databases. Inconsistent or unclear view names can lead to confusion, making it harder for developers and administrators to understand the purpose and use of each view. This ultimately impacts the efficiency and accuracy of database management and usage.

For example:

SQL
Copy
1-- Example of a view with a non-descriptive name
2CREATE VIEW View1 AS
3SELECT * FROM Customers WHERE Active = 1;

This query demonstrates a poor naming convention. Using a non-descriptive name like View1 does not convey the intent or focus of the view, which can cause confusion when revisiting the code or when collaborating with others.

  • Hard to identify the purpose or logic of views, leading to increased cognitive load and potential errors.

  • Challenges in adhering to best practices for database standards and making maintenance tasks more complex.

How to fix

Ensure that view names conform to the established naming conventions to improve comprehension and maintainability of the database.

Follow these steps to address the issue:

  1. Analyze the existing view name and identify if it aligns with your organization’s naming conventions. For example, views that represent active customers could be prefixed with vw_ followed by a descriptive name.

  2. Use the sp_rename system stored procedure to rename views with non-descriptive names to more meaningful ones. Ensure that the new name accurately reflects the content or purpose of the view.

  3. Review and update any dependencies that might reference the old view name, such as stored procedures, functions, or applications, to ensure they use the new name.

For example:

SQL
Copy
1-- Example of renaming a view to follow naming conventions
2EXEC sp_rename 'View1', 'vw_ActiveCustomers';

Scope

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

Parameters
Name Description Default Value
NamePattern

View name pattern.

regexp:v[A-Z][A-Za-z1-9_]+
SchemaQualifiedNamePattern

Schema qualified name pattern.

–
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 VIEW dbo.Customers
 2AS
 3--Select from local member table.
 4SELECT *
 5FROM CompanyData.dbo.Customers_33
 6UNION ALL
 7--Select from member table on Server2.
 8SELECT *
 9FROM Server2.CompanyData.dbo.Customers_66
10UNION ALL
11--Select from mmeber table on Server3.
12SELECT *
13FROM Server3.CompanyData.dbo.Customers_99
Analysis Results
  Message Line Column
1 SA0063B : The view [dbo].[Customers] does not match the naming convention. The expected key name is [v[A-Z][A-Za-z1-9_]+]. 1 16
See Also

Other Resources

Analysis Rules