利用 Excel 產生 SQL Server 資料字典與報表：擴展屬性自動化腳本

核心思路

SQL Server 擴展屬性 可為資料庫/資料表/欄位附加描述。透過系統函數 fn_listextendedproperty 擷取，寫入暫存表，再由 Excel/報表服務匯出，即可產生完整資料字典。

自動化存儲過程：sp_get_extendedproperty

CREATE PROCEDURE [dbo].[sp_get_extendedproperty]
    @databasename VARCHAR(128) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    IF @databasename IS NULL SET @databasename = DB_NAME();

    -- 建立暫存表
    CREATE TABLE ##DataDict (
        tableschema VARCHAR(128), tablename VARCHAR(128), columnname VARCHAR(128),
        xtype VARCHAR(8), description NVARCHAR(4000)
    );
    CREATE TABLE ##SchemaList (tableschema VARCHAR(128), tablename VARCHAR(128));
    CREATE TABLE ##KeyCols (tableschema VARCHAR(128), tablename VARCHAR(128), columnname VARCHAR(128), xtype VARCHAR(8));

    DECLARE @sql NVARCHAR(MAX);

    -- 1. 資料庫層級描述
    SET @sql = N'INSERT INTO ##DataDict (description, xtype) SELECT CAST(value AS NVARCHAR(4000)), ''D'' FROM ' 
        + QUOTENAME(@databasename) + N'.sys.fn_listextendedproperty(default, default, default, default, default, default, default)';
    EXEC(@sql);

    -- 2. 取得所有使用者資料表
    SET @sql = N'INSERT INTO ##SchemaList SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM ' 
        + QUOTENAME(@databasename) + N'.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''';
    EXEC(@sql);

    -- 3. 資料表層級描述
    DECLARE cur CURSOR FAST_FORWARD FOR SELECT DISTINCT tableschema FROM ##SchemaList;
    DECLARE @sch VARCHAR(128);
    OPEN cur; FETCH NEXT FROM cur INTO @sch;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'INSERT INTO ##DataDict (tableschema, tablename, description, xtype) SELECT ''' + @sch + N''', objname, CAST(value AS NVARCHAR(4000)), ''U'' FROM ' 
            + QUOTENAME(@databasename) + N'.sys.fn_listextendedproperty(NULL, ''schema'', ''' + @sch + N''', ''table'', default, NULL, NULL)';
        EXEC(@sql);
        FETCH NEXT FROM cur INTO @sch;
    END
    CLOSE cur; DEALLOCATE cur;

    -- 4. 主鍵/外鍵欄位標記
    SET @sql = N'INSERT INTO ##KeyCols SELECT U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME, O.xtype FROM ' 
        + QUOTENAME(@databasename) + N'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U JOIN ' 
        + QUOTENAME(@databasename) + N'.sys.sysobjects O ON U.CONSTRAINT_NAME = O.name WHERE O.xtype IN (''PK'',''F'')';
    EXEC(@sql);

    -- 5. 欄位層級描述
    DECLARE @tbl VARCHAR(128);
    DECLARE cur2 CURSOR FAST_FORWARD FOR SELECT tableschema, tablename FROM ##SchemaList;
    OPEN cur2; FETCH NEXT FROM cur2 INTO @sch, @tbl;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'INSERT INTO ##DataDict (tableschema, tablename, columnname, description) SELECT ''' + @sch + N''', ''' + @tbl + N''', objname, CAST(value AS NVARCHAR(4000)) FROM ' 
            + QUOTENAME(@databasename) + N'.sys.fn_listextendedproperty(NULL, ''schema'', ''' + @sch + N''', ''table'', ''' + @tbl + N''', ''column'', default)';
        EXEC(@sql);
        FETCH NEXT FROM cur2 INTO @sch, @tbl;
    END
    CLOSE cur2; DEALLOCATE cur2;

    -- 6. 合併鍵類型
    UPDATE d SET d.xtype = k.xtype FROM ##DataDict d JOIN ##KeyCols k 
        ON d.tableschema=k.tableschema AND d.tablename=k.tablename AND d.columnname=k.columnname;

    -- 7. 輸出結果
    SELECT @@SERVERNAME AS servername, @databasename AS dbname, tableschema, tablename, columnname, xtype, description
    FROM ##DataDict ORDER BY servername, dbname, tableschema, tablename, columnname, xtype;

    DROP TABLE ##DataDict, ##SchemaList, ##KeyCols;
END

使用方式

1. SSMS 執行上述腳本建立存儲過程。
2. 執行 EXEC sp_get_extendedproperty 'YourDB'。
3. 結果複製到 Excel，或設為 SSRS/報表資料來源。
4. 依 xtype 篩選：D=資料庫、U=資料表、PK=主鍵、F=外鍵、空白=一般欄位。

擴展層性維護技巧

-- 新增/更新資料表描述
EXEC sys.sp_addextendedproperty 'MS_Description', '客戶基本資料', 'schema', 'dbo', 'table', 'Customers';
EXEC sys.sp_updateextendedproperty 'MS_Description', '客戶基本資料 (含聯絡人)', 'schema', 'dbo', 'table', 'Customers';

-- 新增欄位描述
EXEC sys.sp_addextendedproperty 'MS_Description', '電子郵件地址', 'schema', 'dbo', 'table', 'Customers', 'column', 'Email';

-- 刪除
EXEC sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'Customers', 'column', 'Email';

優化建議

• 加入 sys.columns / sys.types 聯結，輸出資料類型、長度、精度、是否允許 NULL、預設值、Identity 資訊。
• 使用 sp_msforeachtable 簡化游標邏輯 (注意未文件化風險)。
• 大型資料庫建議分批次輸出，避免單一結果集過大。