~楓花雪岳~: [SQL] 擴充屬性

星期五, 12月 18, 2015

[SQL] 擴充屬性

Turning 時，對 Index 進行修正，突然一個念頭是，該 Index 到底是甚麼時候建立、修改的，想要紀錄該時間點，才查到有擴充屬性（Extended Properties）可以使用

操作擴充屬性的三個功能，分別為 sp_addextendedproperty、sp_updateextendedproperty 和 sp_dropextendedproperty，在 MSDN 說明上，都會有 Level0、Level1 和 Level2 參數需要設定，以 sp_addextendedproperty 為例說明

在這篇文章 - Document Your SQL Server Databases with Extended Properties 內，有把三層 Level 的物件，利用圖表方式來表達，清楚明瞭，看完就知道 Level0、Level1 和 Level2 參數，要如何設定

在 AdventureWorks2014 內對 [HumanResources].[Employee] 建立 IX_Employee_HireDate Index，並對該 Index 建立 IndexCreateDate 擴充屬性

USE [AdventureWorks2014]
GO

-- 建立 Index
CREATE INDEX IX_Employee_HireDate ON [HumanResources].[Employee] (HireDate)

-- 執行 sp_addextendedproperty、sp_updateextendedproperty 和 sp_dropextendedproperty 後，會利用下列語法查詢執行結果
SELECT 
    class, 
    class_desc, 
    major_id, 
    minor_id, 
    ep.name, 
    s.name AS [Index Name], 
    value
FROM sys.extended_properties AS ep
    INNER JOIN sys.indexes AS s ON ep.major_id = s.object_id 
                                   AND ep.minor_id = s.index_id
WHERE class = 7
    AND S.name = 'IX_Employee_HireDate'
GO

-- 新增擴充屬性 - IndexCreateDate
EXEC sp_addextendedproperty 
    @name = N'IndexCreateDate', 
    @value = '20151218',
    @level0type = N'Schema', @level0name = 'HumanResources',
    @level1type = N'Table',  @level1name = 'Employee',
    @level2type = N'Index', @level2name = 'IX_Employee_HireDate';
GO

-- 更新擴充屬性 IndexCreateDate 的值為 20151218 V2
EXEC sp_updateextendedproperty
    @name = N'IndexCreateDate', 
    @value = '20151218 V2', -- 相較於新增，只有 value 有差異
    @level0type = N'Schema', @level0name = 'HumanResources',
    @level1type = N'Table',  @level1name = 'Employee',
    @level2type = N'Index', @level2name = 'IX_Employee_HireDate';
GO

-- 刪除擴充屬性 IndexCreateDate，不需要 value 參數
EXEC sp_dropextendedproperty 
    @name = N'IndexCreateDate', 
    @level0type = N'Schema', @level0name = 'HumanResources',
    @level1type = N'Table',  @level1name = 'Employee',
    @level2type = N'Index', @level2name = 'IX_Employee_HireDate';
GO