[SQL] 複製欄位描述

論壇問題

要把 Source DB Table 欄位描述複製至目地 DB Table 中

測試環境簡易說明

須先建立 Source DB 和 Destination DB ，兩個 DB 中內含的 Employ Table

USE [Source] -- 須先建立 Source DB 和 Destination DB，然後根據需求來切換 DB
GO

-- 建立 Employ Table
CREATE TABLE [dbo].[Employ]
(
    [EmpNO] [int] NOT NULL,
    [EmpName] [nvarchar](20) NULL,
    [Birthday] [date] NULL,
    CONSTRAINT [PK_Employ] PRIMARY KEY CLUSTERED 
    (
        [EmpNO] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Destination DB 內的 Employ 則不需要下面這段
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'員工編號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employ', @level2type=N'COLUMN',@level2name=N'EmpNO'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'員工姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employ', @level2type=N'COLUMN',@level2name=N'EmpName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employ', @level2type=N'COLUMN',@level2name=N'Birthday'
GO

實際更新測試

關鍵三語法為 ::fn_listextendedproperty、sp_updateextendedproperty 和 sp_addextendedproperty，接不可以跨 DB 使用，因此必須利用 USE DB 來切換

-- #Temp 用來接收來源 DB 的 Table 欄位描述
IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
    DROP TABLE #Temp

CREATE TABLE #Temp (SchemaName nvarchar(128),TableName sysname,ColumnName nvarchar( 128),ColumnDescript sql_variant)

-- 切換至來源 DB
USE [Source]

-- 把欲更新的 Table 欄位描述，放進 #Temp 內
INSERT INTO #Temp (SchemaName,TableName,ColumnName,ColumnDescript)
SELECT 
    IST.Table_schema,
    IST.Table_name,
    ISC.COLUMN_NAME,
    ( 
        SELECT value
        FROM fn_listextendedproperty (NULL, 'schema', IST.Table_schema, 'table', IST.TABLE_NAME, 'column', default)
        WHERE name = 'MS_Description'
                and objtype='COLUMN'
                and objname Collate Chinese_Taiwan_Stroke_CI_AS = ISC.COLUMN_NAME
     )
FROM INFORMATION_SCHEMA.TABLES IST
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS ISC ON IST.TABLE_NAME = ISC.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
    AND IST.Table_name = 'Employ' -- 指定資料表名稱

-- 顯示 #Temp 內的資料
SELECT * FROM #Temp

-- 切至 Destination
USE [Destination]

-- 對 #Temp Table 跑 cursor
DECLARE curTemp CURSOR   -- 宣告 Cursor 及其資料來源
FOR
    (
        SELECT SchemaName,TableName,ColumnName,ColumnDescript
        FROM #Temp
    )

OPEN curTemp -- 打開 Cursor，並建立 Cursor 與資料表關連

DECLARE 
    @SchemaName as nvarchar(128),
    @TableName AS sysname,
    @ColumnName AS nvarchar(128),
    @ColumnDescript AS sql_variant

FETCH NEXT FROM curTemp INTO @SchemaName,@TableName,@ColumnName,@ColumnDescript -- 將資料存進變數中
WHILE (@@FETCH_STATUS = 0) -- 檢查是否有讀取到資料
BEGIN

    -- 判斷該欄位屬性是否存在
    if exists 
    (
        select 1 
        from ::fn_listextendedproperty 
             (
                 N'MS_Description',
                 'SCHEMA',@SchemaName,
                 'TABLE',@TableName,
                 'COLUMN',@ColumnName
             )
    )
        -- 存在：更新 MS_Description 屬性資料
        EXEC sp_updateextendedproperty N'MS_Description',@ColumnDescript,N'SCHEMA',@SchemaName,N'TABLE',@TableName,N'COLUMN',@ColumnName
    ELSE
        -- 不存在：新增 MS_Description 屬性資料
        EXEC sp_addextendedproperty N'MS_Description', @ColumnDescript, N'SCHEMA',@SchemaName, N'TABLE', @TableName, N'COLUMN', @ColumnName

    FETCH NEXT FROM curTemp INTO @SchemaName,@TableName,@ColumnName,@ColumnDescript
END

CLOSE curTemp -- 關閉 Cursor，並關閉 Cursor 與資料表連結
DEALLOCATE curTemp -- 將 Cursor 物件移除

-- 顯示 [Destination] 內 Table 的欄位資訊
SELECT 
    IST.Table_schema,
    IST.Table_name,
    ISC.COLUMN_NAME,
    (  
        SELECT value
        FROM fn_listextendedproperty (NULL, 'schema', IST.Table_schema, 'table', IST.TABLE_NAME, 'column', default)
        WHERE name = 'MS_Description'
                and objtype='COLUMN'
                and objname Collate Chinese_Taiwan_Stroke_CI_AS = ISC.COLUMN_NAME
    )
FROM INFORMATION_SCHEMA.TABLES IST
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS ISC ON IST.TABLE_NAME = ISC.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
    AND IST.Table_name = 'Employ' -- 指定資料表名稱

參考資料
• 論壇問題出處
• 取得、新增、更新MS SQL中資料欄位的描述內容