取得、新增、更新MS SQL中資料欄位的描述內容

--List DBNAME
select name from master.dbo.sysdatabases  

--List Table name
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable 
FROM sys.tables   

--List Column Property  
SELECT a.Table_schema +'.'+a.Table_name   as 表格名稱  
       ,b.COLUMN_NAME                             as 欄位名稱  
       ,b.DATA_TYPE                                    as 資料型別  
       ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as 長度  
       ,isnull(b.COLUMN_DEFAULT,'')           as 預設值  
       ,b.IS_NULLABLE                                 as 允許空值  
       ,( SELECT value  
          FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)  
          WHERE name='MS_Description' and objtype='COLUMN'   
          and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME  
        ) as 欄位描述  
FROM INFORMATION_SCHEMA.TABLES  a  
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME  
WHERE TABLE_TYPE='BASE TABLE' and  a.Table_name='code'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION 

 

--新增或修改資料表說明

IF not exists(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '資料表名稱', NULL, NULL)) 
BEGIN
  exec sp_addextendedproperty 'MS_Description', '資料表說明', 'user', 'dbo', 'table', '資料表名稱'
END
ELSE
BEGIN
  exec sp_updateextendedproperty 'MS_Description', '資料表說明', 'user', 'dbo', 'table', '資料表名稱'
END

 

--新增或修改欄位說明

IF not exists(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱'))
BEGIN
  exec sp_addextendedproperty 'MS_Description', '欄位說明', 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱'
END
ELSE
BEGIN
  exec sp_updateextendedproperty 'MS_Description', '欄位說明', 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱'
END

::fn_listextendedproperty用法

--取得資料表單一欄位說明

SELECT value FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱')

--取得資料表所有欄位說明

SELECT * FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '資料表名稱', 'column', NULL)

--取得資料表說明

SELECT * FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '資料表名稱', NULL, NULL)

--取得所有資料表說明

SELECT * FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', NULL, NULL, NULL)

--上面使用NULL的參數,可以改用default 代替

SELECT * FROM ::fn_listextendedproperty(default, 'user', 'dbo', 'table', default, default, default)