檢視並匯出SQL Server 2000資料表中欄位的註釋資訊:
此示例為匯出某個表註釋的語句:(表名是bbs_bank_log)
SELECT sysobjects.name AS 表名, syscolumns.name AS 列名,
systypes.name AS 資料型別, syscolumns.length AS 資料長度, CONVERT(char,
sysproperties.[value]) AS 註釋
FROM sysproperties RIGHT OUTER JOIN
sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype ON
sysproperties.id = syscolumns.id AND
sysproperties.smallid = syscolumns.colid
WHERE (sysobjects.xtype = "u" OR
sysobjects.xtype = "v") AND (systypes.name "sysname")
--and CONVERT(char,sysproperties.[value]) "null" --匯出註釋不為"null"的記錄
--AND (sysobjects.name = "bbs_bank_log") --逐個關聯表名,可以用or連線條件
ORDER BY 表名
注意事項:
上文中的語句是匯出某個表的所有註釋,如果你需要簡單的列出表的所有註釋,語句如下:
SELECT CONVERT(char, [value]) AS Expr1 FROM sysproperties
sysobjects是系統物件表,syscolumns是系統欄位資訊表,systypes是系統型別表。透過欄位的ID和sysproperties(系統註釋屬性表)關聯,即可讀出註釋資訊。
檢視並匯出SQL Server 2005以上資料表中欄位的註釋資訊:
select minor_id,value from sys.extended_properties
檢視並匯出SQL Server 2000資料表中欄位的註釋資訊:
此示例為匯出某個表註釋的語句:(表名是bbs_bank_log)
SELECT sysobjects.name AS 表名, syscolumns.name AS 列名,
systypes.name AS 資料型別, syscolumns.length AS 資料長度, CONVERT(char,
sysproperties.[value]) AS 註釋
FROM sysproperties RIGHT OUTER JOIN
sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype ON
sysproperties.id = syscolumns.id AND
sysproperties.smallid = syscolumns.colid
WHERE (sysobjects.xtype = "u" OR
sysobjects.xtype = "v") AND (systypes.name "sysname")
--and CONVERT(char,sysproperties.[value]) "null" --匯出註釋不為"null"的記錄
--AND (sysobjects.name = "bbs_bank_log") --逐個關聯表名,可以用or連線條件
ORDER BY 表名
注意事項:
上文中的語句是匯出某個表的所有註釋,如果你需要簡單的列出表的所有註釋,語句如下:
SELECT CONVERT(char, [value]) AS Expr1 FROM sysproperties
sysobjects是系統物件表,syscolumns是系統欄位資訊表,systypes是系統型別表。透過欄位的ID和sysproperties(系統註釋屬性表)關聯,即可讀出註釋資訊。
檢視並匯出SQL Server 2005以上資料表中欄位的註釋資訊:
select minor_id,value from sys.extended_properties