Click or drag to resize

SA0066B : Check all Columns for following specified naming convention.(Batch)

The topic describes the SA0066B analysis rule.

Message

Check all Columns for following specified naming convention.(Batch)

Description

The rule checks the naming of all columns in thhe SQL code.

The following parameters can be used to select or define the desired pattern which will be used to check the object name:

- GeneralColumnNamePattern - applied for all columns that do not fall in the other categories

- PrimaryKeyColumnNamePattern - applied for primary key columns

- ForeignKeyColumnNamePattern - applied for foreign key columns

- BitColumnNamePattern - applied for columns which are of data type Bit

- StringColumnNamePattern - applied for string columns

- DateTimeColumnNamePattern - applied for date time columns

- NumericColumnNamePattern - applied for numeric data type columns

- BinaryColumnNamePattern - applied for binary data type columns

- GeographyColumnNamePattern - applied for geography data type columns

- HierarchyidColumnNamePattern - applied for HierarchyId data type columns

- UniqueidentifierColumnNamePattern - applied for uniqueidentifier data type columns

- XmlColumnNamePattern - applied for Xml data type columns

- SqlVariantColumnNamePattern - applied for sql_variant data type columns

- TableColumnNamePattern - applied for table data type columns

- TimestampColumnNamePattern - applied for Timestamp data type columns

Regular expression patterns can be used, but the pattern must be prefixed with 'regexp:' string in order to be used as a matching regular expression.

Scope

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

Parameters
NameDescriptionDefault Value
GeneralColumnNamePattern

General column name pattern.

regexp:[a-z]+

PrimaryKeyColumnNamePattern

Primary key column name pattern.

{table_name}ID

ForeignKeyColumnNamePattern

Foreign key column name pattern.

{referenced_table}_{referenced_column}

BitColumnNamePattern

Bit column name pattern.

regexp:[A-Z][A-Za-z1-9]+

StringColumnNamePattern

String column name pattern.

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

DateTimeColumnNamePattern

Datetime column name pattern.

regexp:[A-Z][A-Za-z1-9]+

NumericColumnNamePattern

Numeric column name pattern.

regexp:[A-Z][A-Za-z1-9]+

BinaryColumnNamePattern

Binary column name pattern.

regexp:[A-Z][A-Za-z1-9]+

GeographyColumnNamePattern

Geography column name pattern.

regexp:[A-Z][A-Za-z1-9]+

HierarchyidColumnNamePattern

Hierarchyid column name pattern.

regexp:[A-Z][A-Za-z1-9]+

UniqueidentifierColumnNamePattern

Uniqueidentifier column name pattern.

regexp:[A-Z][A-Za-z1-9]+

XmlColumnNamePattern

Xml column name pattern.

regexp:[A-Z][A-Za-z1-9]+

SqlVariantColumnNamePattern

Sql_Variant column name pattern.

regexp:[A-Z][A-Za-z1-9]+

TableColumnNamePattern

Table column name pattern.

regexp:[A-Z][A-Za-z1-9]+

TimestampColumnNamePattern

Timestamp column name pattern.

regexp:[A-Z][A-Za-z1-9]+

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Naming Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1ALTER TABLE Persons
 2ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName),
 3 DateOfBirth datetime
 4
 5ALTER TABLE Persons
 6ADD CONSTRAINT PK_Person PRIMARY KEY (ID);
 7
 8ALTER TABLE Persons
 9ADD FOREIGN KEY (OrderId) REFERENCES Orders(Id);
10
11ALTER TABLE Persons
12ADD CONSTRAINT FK_OrderPerson FOREIGN KEY (OrderId) REFERENCES Orders(Id);
13
14ALTER TABLE Persons
15ADD DateOfBirth datetime;
16
17ALTER TABLE Persons 
18ADD Gender bit NOT NULL CONSTRAINT [Df_Gender] DEFAULT 0
19
20ALTER TABLE Persons
21ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName1),
22FOREIGN KEY (FirstName,LastName) REFERENCES Persons(FirstName,LastName);
23
24ALTER TABLE Persons
25ALTER COLUMN Position nchar;
26
27ALTER TABLE Persons
28ALTER COLUMN DateOfBirth year;
29
30ALTER TABLE Persons
31ALTER COLUMN Salary money;
32
33CREATE TABLE Persons (
34    Id int NOT NULL,
35    OrderId int,
36    FOREIGN KEY (OrderId) REFERENCES Orders(Id),
37    PRIMARY KEY (Id)
38)
39
40CREATE TABLE Persons (
41    ID int NOT NULL IDENTITY (1,1) PRIMARY KEY,
42    OrderId int FOREIGN KEY REFERENCES Orders(Id),
43    LastName varchar(255) NOT NULL,
44    MiddleName ntext,
45    FirstName nvarchar(255),
46    Position char,
47    Remark text,
48    Age smallint,
49    Salary numeric
50);
51
52CREATE TABLE Persons (
53    Id int NOT NULL,
54    OrderId int,
55    LastName varchar(255) NOT NULL,
56    CONSTRAINT FK_PersonOrder FOREIGN KEY (OrderId) REFERENCES Orders(Id),
57    CONSTRAINT PK_Person PRIMARY KEY (Id,LastName)
58)
59
60ALTER TABLE Persons
61ADD PRIMARY KEY (ID);
62
63ALTER TABLE Persons
64DROP COLUMN DateOfBirth;
65
66INSERT INTO Persons (Remark) 
67SELECT 'Hello!'
68UNION ALL 
69SELECT 'Hi!'
70UNION ALL
71SELECT 'Hello, world!'
72
73INSERT INTO Persons (Remark) 
74VALUES ('How do yo do?'),
75        ('Good morning!'),
76        ('Good night!')
77
78DELETE  Persons WHERE Id = 3
79
80SELECT * FROM Persons p 
81WHERE p.Remark like 'Hello%'
82
83DROP TABLE Persons

Analysis Results

 MessageLineColumn
1SA0066B : The primary key column [Persons].[ID] does not match the naming convention. The expected name is [PersonsID].238
2SA0066B : The primary key column [Persons].[LastName] does not match the naming convention. The expected name is [PersonsID].241
3SA0066B : The primary key column [Persons].[ID] does not match the naming convention. The expected name is [PersonsID].638
4SA0066B : The foreign key column [Persons].[OrderId] does not match the naming convention. The expected name is [Orders_Id].917
5SA0066B : The foreign key column [Persons].[OrderId] does not match the naming convention. The expected name is [Orders_Id].1243
6SA0066B : The primary key column [Persons].[ID] does not match the naming convention. The expected name is [PersonsID].2138
7SA0066B : The primary key column [Persons].[LastName1] does not match the naming convention. The expected name is [PersonsID].2141
8SA0066B : The foreign key column [Persons].[FirstName] does not match the naming convention. The expected name is [Persons_FirstName].2213
9SA0066B : The foreign key column [Persons].[LastName] does not match the naming convention. The expected name is [Persons_FirstName].2223
10SA0066B : The primary key column [Persons].[Id] does not match the naming convention. The expected name is [PersonsID].344
...
16SA0066B : The primary key column [Persons].[LastName] does not match the naming convention. The expected name is [PersonsID].554
17SA0066B : The primary key column [Persons].[ID] does not match the naming convention. The expected name is [PersonsID].6117
See Also

Other Resources