SA0085 : Check database objects for missing specific extended properties

Introduction

Ensure database objects have extended properties to improve self-documenting databases and tool-based documentation.

Description

In the context of T-SQL and SQL Server, extended properties allow you to store additional metadata about database objects. These properties are crucial for documentation purposes, can enhance data management, and facilitate automatic documentation generation by third-party tools. Not assigning necessary extended properties can result in incomplete or inaccurate documentation, affecting maintenance, clarity, and tool integration.

For example:

-- Example of a query creating a table without extended properties
CREATE TABLE Employee (
    EmployeeID int PRIMARY KEY,
    FirstName nvarchar(50),
    LastName nvarchar(50)
);

-- Missing extended properties that describe columns or the table itself

In this example, the Employee table is created without any extended properties. This omission means that no further metadata is available to describe the purpose or usage of the table and its columns, which could lead to misunderstandings or require additional efforts to manage documentation manually.

Lack of extended properties results in poor self-documentation, leading to misunderstandings about the database schema.
Absence of metadata may hinder automated documentation tools, complicating database administration and maintenance.

How to fix

Ensure that database objects in SQL Server are equipped with extended properties for accurate documentation and metadata management.

Follow these steps to address the issue:

1.Identify the database objects that lack extended properties. These can include tables, views, or any other database object where metadata would be beneficial.

2.Use the sp_addextendedproperty stored procedure to add descriptions and metadata to each necessary object. Specify the name of the property and the value you wish to assign to provide documentation details.

3.To suppress checking of a specific object type where extended properties are not needed, set the corresponding required properties parameter to an empty value. This step allows you to focus on only the necessary objects.

For example:

-- Adding extended properties to the Employee table
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Table containing employee data',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'Employee';

-- Adding extended properties to a specific column
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'The unique identifier for an employee',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'Employee',
    @level2type = N'COLUMN',
    @level2name = N'EmployeeID';

Scope

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

Parameters

Name	Description	Default Value
DatabaseRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
FunctionRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
StoredProcedureRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
ViewRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
TableRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
TriggerRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
DatabasePrincipalRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
SchemaRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
DataTypeRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
AssemblyRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
MessageTypeRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
RuleRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
DefaultRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
ServiceRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
ServiceBindingRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
ServiceContractRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
RouteRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
XmlSchemaCollectionRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
DataSpaceRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
FileGroupRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
PartitionFunctionRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
DatabaseFileRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
PrimaryKeyConstraintRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
CheckConstraintRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
ForeignKeyConstraintRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
SynonymRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
IndexRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
ColumnRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
ParameterRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
UniqueConstraintRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description
DefaultConstraintRequiredProperties	Comma separated list of required extended properties for the object type.	MS_Description

Remarks

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

Effort To Fix

5 minutes per issue.

Additional Information

There is no additional info for this rule.

Analysis Rules