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 (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.
|
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.

















Hamid KhanPosted Aug 24, 2024, 5:09 AM
Very Good Article