----------
DataBase, Table, Column中的Description

以下指令:
若為SQL2000, SQL2005, 則使用'user',
若為SQL2005, SQL2008, 則使用'schema'. 
fn_listextendedproperty()中, null或defaul均可使用.

USE Northwind
go

--Get description in a column.
SELECT * FROM ::fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', 'orders', 'column', 'CustomerID')

--Get column descriptions in a table.
SELECT * FROM ::fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', 'orders', 'column', null)

--Get descriptions in a table.
SELECT * FROM ::fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', 'orders', null, null)

--Get table descriptions in a database.
SELECT * FROM ::fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', null, null, null)


--Add/Update description in a table.
IF not exists(SELECT * FROM ::fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', 'orders', NULL, NULL))  
    BEGIN  
        exec sp_addextendedproperty 'MS_Description', 'Description...', 'schema', 'dbo', 'table', 'orders' 
    END  
ELSE 
    BEGIN  
        exec sp_updateextendedproperty 'MS_Description', 'Description...', 'schema', 'dbo', 'table', 'orders' 
    END


--Add/Update description in a column.
IF not exists(SELECT * FROM ::fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'orders', 'column', 'CustomerID')) 
    BEGIN  
        exec sp_addextendedproperty 'MS_Description', 'Description...', 'schema', 'dbo', 'table', 'orders', 'column', 'CustomerID'
    END  
ELSE 
    BEGIN  
        exec sp_updateextendedproperty 'MS_Description', 'Description...', 'schema', 'dbo', 'table', 'orders', 'column', 'CustomerID'
    END


----------
顯示資料庫物件本身所設定的所有擴充屬性

USE AdventureWorks2012;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default, default, default, default);
GO

----------
列出 ScrapReason 資料表中之資料行的擴充屬性。
這包含在 Production 結構描述中

USE AdventureWorks2012;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', default);
GO

----------
列出 Sales 結構描述中之所有資料表的擴充屬性

USE AdventureWorks2012;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', default, NULL, NULL);
GO