SQL Server 將描述信息存儲在擴展屬性中,類別為 MS_Description。主流查詢關聯三張系統表:
SYS.OBJECTS(或舊 SYSOBJECTS):對象基本信息。SYS.COLUMNS(或舊 SYSCOLUMNS):列信息,COLUMN_ID 對應 SMALLID。SYS.EXTENDED_PROPERTIES(或舊 SYSPROPERTIES):屬性值存儲。SELECT
O.NAME AS TableName,
C.NAME AS ColumnName,
EP.VALUE AS Description
FROM SYS.EXTENDED_PROPERTIES EP
JOIN SYS.OBJECTS O ON O.OBJECT_ID = EP.MAJOR_ID
JOIN SYS.COLUMNS C ON C.OBJECT_ID = EP.MAJOR_ID AND C.COLUMN_ID = EP.MINOR_ID
WHERE EP.NAME = 'MS_Description'
AND O.NAME = 'IP_Address'
AND C.NAME = 'Province'
ORDER BY O.NAME;
-- 表級
EXEC SP_ADDEXTENDEDPROPERTY
@NAME = N'MS_Description', @VALUE = N'人員信息表',
@LEVEL0TYPE = N'SCHEMA', @LEVEL0NAME = N'DBO',
@LEVEL1TYPE = N'TABLE', @LEVEL1NAME = N'表名';
-- 列級
EXEC SP_ADDEXTENDEDPROPERTY
@NAME = N'MS_Description', @VALUE = N'姓名',
@LEVEL0TYPE = N'SCHEMA', @LEVEL0NAME = N'DBO',
@LEVEL1TYPE = N'TABLE', @LEVEL1NAME = N'表名',
@LEVEL2TYPE = N'COLUMN', @LEVEL2NAME = N'列名';
-- 修改
EXEC SP_UPDATEEXTENDEDPROPERTY
@NAME = N'MS_Description', @VALUE = N'新描述',
@LEVEL0TYPE = N'SCHEMA', @LEVEL0NAME = N'DBO',
@LEVEL1TYPE = N'TABLE', @LEVEL1NAME = N'表名',
@LEVEL2TYPE = N'COLUMN', @LEVEL2NAME = N'列名';
-- 刪除
EXEC SP_DROPEXTENDEDPROPERTY
@NAME = N'MS_Description',
@LEVEL0TYPE = N'SCHEMA', @LEVEL0NAME = N'DBO',
@LEVEL1TYPE = N'TABLE', @LEVEL1NAME = N'表名',
@LEVEL2TYPE = N'COLUMN', @LEVEL2NAME = N'列名';
CREATE VIEW V_Field_Description AS
SELECT
O.NAME AS TableName,
C.NAME AS ColumnName,
T.NAME AS DataType,
C.MAX_LENGTH,
C.IS_NULLABLE,
EP.VALUE AS Description
FROM SYS.COLUMNS C
JOIN SYS.OBJECTS O ON O.OBJECT_ID = C.OBJECT_ID
JOIN SYS.TYPES T ON T.USER_TYPE_ID = C.USER_TYPE_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES EP
ON EP.MAJOR_ID = C.OBJECT_ID
AND EP.MINOR_ID = C.COLUMN_ID
AND EP.NAME = 'MS_Description'
WHERE O.TYPE = 'U'; -- 僅用戶表
-- 使用
SELECT * FROM V_Field_Description WHERE TableName = 'YourTable';