数据库字典（2）---扩展属性

发布：2024-10-14 22:54 阅读：53 点赞：0

介绍：

本文将讨论数据库字典以及如何从扩展属性中获取字典数据。本文的内容将是

A-什么是数据字典？
B - 什么是扩展属性
C - 操作扩展属性

A - 什么是数据字典？

数据字典可以定义为存储数据库中使用的数据元素的名称、定义和属性集合的组件。数据库存储元数据 --- 有关数据库的信息。

一般来说，元数据是指有关数据的信息。因此，将数据库方案和其他元数据存储在称为数据字典或系统目录的单一结构中。数据字典就像关系数据库系统的 AZ 字典，它存储了数据库中有关每个关系的所有信息。

数据字典由两个词组成：

数据--- 代表从数据库收集的数据；
字典 ---代表这些数据在哪儿可用。

数据字典提供了有关数据库中组件之间关系的附加信息。

下面是描述包含员工详细信息的表的数据字典。

雇员
字段名称	数据类型	显示字段大小	描述	例子
员工编号	整数	8	每个员工的唯一 ID	100025
全名	文本	三十	员工全名	艾米莉·约翰逊
出生日期	日期/时间	10	员工出生日期	1990-05-15
电话号码	整数	10	员工电话号码	555-123-4567

B - 什么是扩展属性

扩展属性允许向数据库对象添加自定义属性。这是 SQL Server 中存储有关数据库对象的更多信息的独特功能。

可以为以下数据库对象创建扩展属性：

数据库
存储过程
用户定义函数
桌子
表格列
表索引
视图
规则
触发器
约束

扩展属性可用于存储额外的数据库对象相关信息，例如：

指定表、视图或列的标题。
指定列的显示掩码。
显示列的格式、定义日期列的编辑掩码、定义小数位数等。
指定在列中显示数据的格式规则。
为所有用户描述特定的数据库对象。

从扩展属性中检索到的信息可以被视为数据库字典。

C - 扩展属性在哪里

对于 SQL Server 中的任何对象，例如下表：右键单击 =>属性：

扩展属性：

可以通过 GUI 添加、修改、检索或删除扩展属性，如下所示。或者也可以通过 T-SQL 进行操作。

C - 操作扩展属性

以下是通过T-SQL对扩展属性的操作。

检索

笔记：

检索自以下两篇文章，第一篇给出了定义，第二篇给出了样本

扩展属性定义 --- sys.extended_properties (Transact-SQL) - SQL Server | Microsoft Learn
示例代码--- 使用 SQL Server 扩展属性 (mssqltips.com)

获取所有数据库级别的扩展属性

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

获取所有架构级别扩展属性

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

获取所有表级扩展属性

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

获取所有列级扩展属性

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

添加

笔记：

添加代码来自下面的添加页面，A，E，F，B，而更新代码和删除代码则从这些添加代码中更新以保持一致。

添加 --- sp_addextendedproperty (Transact-SQL) - SQL Server | Microsoft 学习
- A. 向数据库添加扩展属性
- E.E. 向架构添加扩展属性
- F. 向表添加扩展属性
- B. 向表中的列添加扩展属性
更新 --- sp_updateextendedproperty (Transact-SQL) - SQL Server | Microsoft 学习
删除 --- sp_dropextendedproperty (Transact-SQL) - SQL Server | Microsoft 学习

句法：

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

至数据库

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

结果：

核实：

添加了扩展属性标题：

到架构：

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

转至表格

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

至列

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

更新中：

语法：

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

到数据库

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

核实：

到架构

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

转至表格

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

至列

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

掉落

语法：

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

到数据库

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

核实：

到架构：

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

转至表格

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

至列

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

数据字典 (1) 扩展属性 (1) 元数据 (3) 数据库对象 (1) SQL Server (11) 自定义信息 (1)