Database Dictionary (2) --- Extended Properties

Note: this article is published on 08/23/2024.

This series of articles will discuss Database Dictionary related issues

• Database Dictionary (1) --- Schema
  • Database Dictionary (1-1) --- Server, Database, Schema, Table and Column
  • Database Dictionary (1-2) --- Create a System Data Dictionary Database
    • Database Dictionary (1-2-1) --- Using Dynamic SQL Script 
• Database Dictionary (2) --- Extended Properties --- this article
• Database Dictionary (3) --- Loop Databases and Servers by AI

Introduction:

This article will discuss the database dictionary and how to get the dictionary data from Extended Properties. The content of this article will be

• A - What is Data Dictionary?
• B - What are Extended Properties
• C - Manipulate Extended Properties

A - What is Data Dictionary? [ref]

A data dictionary can be defined as a component that stores a collection of names, definitions, and attributes for data elements used in the database. The database stores metadata --- information about the database. 

In general, metadata refers to information about data. Thus, storing the database scheme and other metadata in a single structure called a data dictionary or system directory. A data dictionary is like an A-Z dictionary of a relational database system that stores all the information about every relationship in the database.

The data dictionary consists of two words:

• Data --- represents data collected from the database;
• Dictionary --- represents where this data is available.

The data dictionary provides additional information about the relationship between conponents in the database. 

Below is a data dictionary that describes the table that contains employee details.

Employees

Field Name	Data Type	Field Size for Display	Description	Example
EmployeeID	Integer	8	Unique ID of each employee	100025
FullName	Text	30	Full name of the employee	Emily Johnson
DOB	Date/Time	10	Date of birth of employee	1990-05-15
PhoneNumber	Integer	10	Phone number of employee	555-123-4567

 

B - What are Extended Properties

Extended properties allow to add custom properties to database objects. It is a unique feature in SQL Server to store more information about database objects. 

Extended Properties can be created for the below database objects:

• Database
• Stored Procedures
• User-defined Functions
• Table
• Table Column
• Table Index
• Views
• Rules
• Triggers
• Constraints

Extended Properties can be used to store extra database object related information, such as:

• Specify a caption for a table, view, or column.
• Specify a display mask for a column.
• Display a format of a column, define edit mask for a date column, define number of decimals, etc.
• Specify formatting rules for displaying the data in a column.
• Describe a specific database objects for all users.

Retrieved information from Exteded properties could be viewed as Database Dictionary.

C - Where are Extended Properties

For any object in SQL Server, such as a table below: right click => Properties:

Extended Property:

Extended properties could be added, modified, retrieved or dropped thought the GUI like this. Or they can be manupulated by T-SQL.

C - Manipulate Extended Properties

The following are manupulation to Extended Properties by T-SQL.

Retrieving

Note:

The retrieving is from the following two article, the first one gives the definition, while the second one gives the samples

• Extended Properties Definition --- sys.extended_properties (Transact-SQL) - SQL Server | Microsoft Learn
• Sample Code --- Working with SQL Server Extended Properties (mssqltips.com)

Get all database level Extended Properties

SELECT
   DB_NAME() AS DatabaseName,
   p.name AS ExtendedPropertyName,
   p.value AS ExtendedPropertyValue
FROM
   sys.extended_properties AS p
WHERE
   p.major_id=0 
   AND p.minor_id=0 
   AND p.class=0
ORDER BY
   [Name] ASC

Get all Schema level Extended Properties

SELECT
   DB_NAME() AS DatabaseName,
   p.name AS ExtendedPropertyName,
   p.value AS ExtendedPropertyValue,
   p.*
FROM
   sys.extended_properties AS p
WHERE
   --p.major_id=0 
   --AND 
   p.minor_id=0 
   AND 
   p.class=3 --- Schema
ORDER BY
   [Name] ASC

Get all table level Extended Properties

SELECT
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,	
   tbl.name AS TableName, 
   p.name AS ExtendedPropertyName,
   CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM
   sys.tables AS tbl
   INNER JOIN sys.extended_properties AS p 
   ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1

Get all column level Extended Properties

SELECT
   SCHEMA_NAME(tbl.schema_id) AS SchemaName,	
   tbl.name AS TableName, 
   clmns.name AS ColumnName,
   p.name AS ExtendedPropertyName,
   CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM
   sys.tables AS tbl
   INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
   INNER JOIN sys.extended_properties AS p 
   ON p.major_id=tbl.object_id AND p.minor_id=clmns.column_id AND p.class=1

Adding

Note:

The adding code is from the Add page below, A, E, F, B, while the updating code and dropping code were updated from these adding code for consistent.

• Add --- sp_addextendedproperty (Transact-SQL) - SQL Server | Microsoft Learn
  • A. Add an extended property to a database
  • E. Add an extended property to a schema
  • F. Add an extended property to a table
  • B. Add an extended property to a column in a table
• Update --- sp_updateextendedproperty (Transact-SQL) - SQL Server | Microsoft Learn
• Drop --- sp_dropextendedproperty (Transact-SQL) - SQL Server | Microsoft Learn

Syntax:

sp_addextendedproperty
    [ @name = ] N'name'
    [ , [ @value = ] value ]
    [ , [ @level0type = ] 'level0type' ]
    [ , [ @level0name = ] N'level0name' ]
    [ , [ @level1type = ] 'level1type' ]
    [ , [ @level1name = ] N'level1name' ]
    [ , [ @level2type = ] 'level2type' ]
    [ , [ @level2name = ] N'level2name' ]
[ ; ]

To Database

-- A. Add an extended property to a database

USE AdventureWorks2022;
GO

--Add a caption to the AdventureWorks2022 Database object itself.
EXEC sp_addextendedproperty 
	@name = N'Caption',
    @value = 'AdventureWorks2022 Sample OLTP Database';
GO

Result:

Verify:

Extended Property Caption added:

to Schema:

--	E. Add an extended property to a schema

USE AdventureWorks2022;
GO

EXEC sys.sp_addextendedproperty 
	@name = N'MS_DescriptionExample',
    @value = N'Contains objects related to employees and departments.',
    @level0type = 'SCHEMA', @level0name = N'HumanResources';
GO

to Table

-- F. Add an extended property to a table

USE AdventureWorks2022;
GO

EXEC sys.sp_addextendedproperty 
	@name = N'MS_DescriptionExample',
    @value = N'Street address information for customers, employees, and vendors.',
    @level0type = 'SCHEMA', @level0name = N'Person',
    @level1type = 'TABLE', @level1name = N'Address';
GO

to Column

-- B. Add an extended property to a column in a table

USE AdventureWorks2022;
GO

EXEC sp_addextendedproperty 
	@name = N'Caption',
    @value = 'Postal code is a required column.',
    @level0type = 'SCHEMA', @level0name = N'Person',
    @level1type = 'TABLE', @level1name = N'Address',
    @level2type = 'COLUMN', @level2name = N'PostalCode';
GO

Updating:

Syntax:

sp_updateextendedproperty
    [ @name = ] N'name'
    [ , [ @value = ] value ]
    [ , [ @level0type = ] 'level0type' ]
    [ , [ @level0name = ] N'level0name' ]
    [ , [ @level1type = ] 'level1type' ]
    [ , [ @level1name = ] N'level1name' ]
    [ , [ @level2type = ] 'level2type' ]
    [ , [ @level2name = ] N'level2name' ]
[ ; ]

to Database

-- A. Update an extended property to a database

USE AdventureWorks2022;
GO

-- Update a caption to the AdventureWorks2022 Database object itself.
EXEC sp_updateextendedproperty 
	@name = N'Caption' --,
    @value = 'AdventureWorks2022 Sample OLTP Database. Updated';
GO

Verify:

to Schema

--	E. Update an extended property to a schema

USE AdventureWorks2022;
GO

EXEC sys.sp_updateextendedproperty 
	@name = N'MS_DescriptionExample',
    @value = N'Contains objects related to employees and departments. Updated',
    @level0type = 'SCHEMA', @level0name = N'HumanResources';
GO

to Table

-- F. Update an extended property to a table

USE AdventureWorks2022;
GO

EXEC sys.sp_updateextendedproperty 
	@name = N'MS_DescriptionExample',
    @value = N'Street address information for customers, employees, and vendors. Updated',
    @level0type = 'SCHEMA', @level0name = N'Person',
    @level1type = 'TABLE', @level1name = N'Address';
GO

to Column

-- B. Update an extended property to a column in a table

USE AdventureWorks2022;
GO

EXEC sp_updateextendedproperty 
	@name = N'Caption',
    @value = 'Postal code is a required column. Updated',
    @level0type = 'SCHEMA', @level0name = N'Person',
    @level1type = 'TABLE', @level1name = N'Address',
    @level2type = 'COLUMN', @level2name = N'PostalCode';
GO

Dropping

Syntax:

sp_dropextendedproperty
    [ @name = ] N'name'
    [ , [ @level0type = ] 'level0type' ]
    [ , [ @level0name = ] N'level0name' ]
    [ , [ @level1type = ] 'level1type' ]
    [ , [ @level1name = ] N'level1name' ]
    [ , [ @level2type = ] 'level2type' ]
    [ , [ @level2name = ] N'level2name' ]
[ ; ]

to Database

-- A. Drop an extended property to a database

USE AdventureWorks2022;
GO

-- Drop a caption to the AdventureWorks2022 Database object itself.
EXEC sp_dropextendedproperty 
	@name = N'Caption' --,
    --@value = 'AdventureWorks2022 Sample OLTP Database. Updated';
GO

Verify:

to Schema:

--	E. Drop an extended property to a schema

USE AdventureWorks2022;
GO

EXEC sys.sp_dropextendedproperty 
	@name = N'MS_DescriptionExample',
    --@value = N'Contains objects related to employees and departments. Updated',
    @level0type = 'SCHEMA', @level0name = N'HumanResources';
GO

to Table

-- F. Drop an extended property to a table

USE AdventureWorks2022;
GO

EXEC sys.sp_dropextendedproperty 
	@name = N'MS_DescriptionExample',
    --@value = N'Street address information for customers, employees, and vendors. Updated',
    @level0type = 'SCHEMA', @level0name = N'Person',
    @level1type = 'TABLE', @level1name = N'Address';
GO

to Column

-- B. Drop an extended property to a column in a table

USE AdventureWorks2022;
GO

EXEC sp_dropextendedproperty 
	@name = N'Caption',
    --@value = 'Postal code is a required column. Updated',
    @level0type = 'SCHEMA', @level0name = N'Person',
    @level1type = 'TABLE', @level1name = N'Address',
    @level2type = 'COLUMN', @level2name = N'PostalCode';
GO

References:

• SQL Server Extended Property
  • Create SQL Server Data Dictionary in Seconds (mssqltips.com)
  • Working with SQL Server Extended Properties (mssqltips.com)
  • Extended Properties --- sys.extended_properties (Transact-SQL) - SQL Server | Microsoft Learn
    • Add --- sp_addextendedproperty (Transact-SQL) - SQL Server | Microsoft Learn
      • A. Add an extended property to a database
      • E. Add an extended property to a schema
      • F. Add an extended property to a table
      • B. Add an extended property to a column in a table
    • Update --- sp_updateextendedproperty (Transact-SQL) - SQL Server | Microsoft Learn
    • Drop --- sp_dropextendedproperty (Transact-SQL) - SQL Server | Microsoft Learn