回覆列表
  • 1 # 使用者9662297337979

    檢視並匯出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

  • 中秋節和大豐收的關聯?
  • 百億規模的基金經理還要不要跟?