2. Các ví dụ

ENTITY INTEGRITY

Kiểm tra PRIMARY KEY

Trường hợp Primary Key là 1 trường duy nhất

DECLARE @PK_FIELD NVARCHAR(50) = 'Id'
DECLARE @TABLE_NAME NVARCHAR(50) = 'Products'

IF EXISTS (
   SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE
      OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
      AND COLUMN_NAME = @PK_FIELD
      AND TABLE_NAME = @TABLE_NAME
) 
BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

Trường hợp Primary Key là nhiều trường thì viết như sau:

DECLARE @PK_FIELD NVARCHAR(50) = 'OrderId, ProductId'
DECLARE @TABLE_NAME NVARCHAR(50) = 'OrderDetails'

IF (SELECT COUNT(*) FROM STRING_SPLIT(@PK_FIELD, ',')) = (
   SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE
      OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
      AND COLUMN_NAME IN (SELECT LTRIM(RTRIM([value])) FROM STRING_SPLIT(@PK_FIELD, ','))
      AND TABLE_NAME = @TABLE_NAME
)
BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

Kiểm tra AUTO INCREMENT

IF EXISTS (
   SELECT * FROM [sys].[identity_columns]
   WHERE object_id = OBJECT_ID('Categories')
      AND [name] = 'Id'
      AND [seed_value] = 1
      AND [increment_value] = 1)
BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

DOMAIN INTEGRITY

Kiểm tra DATA TYPE, DATA LENGTH

Kiểm tra kiểu dữ liệu NVARCHAR(100) của field [Name] trong bảng [Products]

IF EXISTS(
   SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
      TABLE_NAME = 'Products'
      AND COLUMN_NAME = 'Name'
      AND DATA_TYPE = 'NVARCHAR'
      AND CHARACTER_MAXIMUM_LENGTH = 100
) BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

Kiểm tra kiểu dữ liệu MONEY(19, 4) của field [Price] trong bảng [Products]

IF EXISTS(
   SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
      TABLE_NAME = 'Products'
      AND COLUMN_NAME = 'Price'
      AND DATA_TYPE = 'MONEY'
      AND NUMERIC_PRECISION = 19
      AND NUMERIC_SCALE = 4
) BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

Kiểm tra kiểu dữ liệu DECIMAL(18, 2) của field [Discount] trong bảng [Products]

IF EXISTS(
   SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
      TABLE_NAME = 'Products'
      AND COLUMN_NAME = 'Discount'
      AND DATA_TYPE = 'DECIMAL'
      AND NUMERIC_PRECISION = 18
      AND NUMERIC_SCALE = 2
) BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

Kiểm tra kiểu dữ liệu NVARCHAR(MAX) của field [Description] trong bảng [Products]

IF EXISTS(
   SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
      TABLE_NAME = 'Products'
      AND COLUMN_NAME = 'Description'
      AND DATA_TYPE = 'NVARCHAR'
      AND CHARACTER_MAXIMUM_LENGTH = -1
) BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

Kiểm tra NOT NULL

Kiểm tra NOT NULL của field [Stock] trong bảng [Products]

IF EXISTS(
   SELECT COLUMN_NAME, IS_NULLABLE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
      TABLE_NAME = 'Products'
      AND COLUMN_NAME = 'Stock'
      AND IS_NULLABLE = 'NO'
) BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

Kiểm tra DEFAULT VALUE

Kiểm tra DEFAULT VALUE = 0 của field [Discount] trong bảng [Products]

IF EXISTS(
   SELECT COLUMN_NAME, COLUMN_DEFAULT
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE
      TABLE_NAME = 'Products'
      AND COLUMN_NAME = 'Discount'
      AND COLUMN_DEFAULT IS NOT NULL
      AND COLUMN_DEFAULT = '((0))'
) BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END

Kiểm tra CHECK CONSTRANT

Kiểm tra CHECK CONSTRANT của field [Discount] trong bảng [Products]

SELECT NAME, DEFINITION
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID('Products')
AND parent_column_id = COLUMNPROPERTY(OBJECT_ID('Products'), 'Discount', 'ColumnId');

REFERENTIAL INTEGRITY

Kiểm tra FOREIGN KEY

Kiểm tra khóa ngoại của field [SupplierId] trong bảng [Products] trỏ sang bảng [Suppliers] field [Id]

IF EXISTS(
   SELECT
      f.name AS ForeignKey,
      OBJECT_NAME(f.parent_object_id) AS TableName,
      COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
      OBJECT_NAME(f.referenced_object_id) AS ReferencedTableName,
      COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumnName
   FROM
      sys.foreign_keys AS f
      INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
   WHERE
      OBJECT_NAME(f.parent_object_id) = 'Products'
      AND COL_NAME(fc.parent_object_id, fc.parent_column_id) = 'SupplierId'
      AND OBJECT_NAME(f.referenced_object_id) = 'Suppliers'
      AND COL_NAME(fc.referenced_object_id, fc.referenced_column_id) = 'Id'
) BEGIN
   PRINT 'Status = PASSED'
END ELSE BEGIN
   PRINT 'Status = FAILED'
END