萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 擴展屬性實戰:字段/表描述的增刪改查完整指南

核心系統視圖

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';
copyright © 萬盛學電腦網 all rights reserved