做一個簡單的網頁查詢數據庫「數據字典實例詳解」

數據字典可以幫助開發人員理解各個數據項目的類型、數值和它們與現實世界中的對象的關係。做數據庫設計時數據字典是不可或缺的一部分，本文列出了幾種常用數據的相關已有表獲取數據字典的一些腳本，以下腳本僅僅測試了部分數據庫版本，未必全部適配。

SqlServer2000：

        select 
          d.name as tname,
	        字段名 = a.name,
	        類型 = b.name,
	        長度 = columnproperty(a.id, a.name, 'PRECISION'),
	        小數位數 = isnull(columnproperty(a.id, a.name, 'Scale'),0),
	        允許空 = case when a.isnullable = 1 then '√' else '' end,
	        默認值 = isnull(e.text, ''),
	        字段說明 = isnull(g.[value], '')
        from syscolumns a 
        left join systypes b on a.xtype = b.xusertype 
        inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name <> 'dtproperties'
        left join syscomments e on a.cdefault = e.id 
        left join sysproperties g on a.id = g.id and a.colid = g.smallid       
        order by a.id, a.colorder

SqlServer2005以上：

        select 
          d.name as tname,
	        字段名 = a.name,
	        類型 = b.name,
	        長度 = columnproperty(a.id, a.name, 'PRECISION'),
	        小數位數 = isnull(columnproperty(a.id, a.name, 'Scale'),0),
	        允許空 = case when a.isnullable = 1 then '√' else '' end,
	        默認值 = isnull(e.text, ''),
	        字段說明 = isnull(g.[value], '')
        from syscolumns a 
        left join systypes b on a.xtype = b.xusertype 
        inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name <> 'dtproperties'
        left join syscomments e on a.cdefault = e.id 
        left join sys.extended_properties g on a.id = g.major_id and a.colid = g.minor_id 
        order by a.id, a.colorder  

Oracle：

        select 
          a.table_name as tname,
	        a.column_name as 字段名, 
	        a.data_type as 類型,
	        a.data_length as 長度,
	        case when a.data_scale = null then 0 else a.data_scale end as 小數位數,
	        case when a.nullable = 'Y' then '√' else '' end as 允許空,
	        a.data_default as 默認值,
	        case when b.comments = null then '' else b.comments end as 字段說明
        from user_tab_columns A 
        left join user_col_comments B on A.table_name = B.table_name 
        and A.column_name = B.column_name 
        order by column_id   

MySql：

        select 
          a.table_name as tname,
          a.column_name as 字段名, 
          a.data_type as 類型,
          a.character_maximum_length as 長度,
          a.numeric_scale as 小數位數,
          case when a.is_nullable = 'YES' then '√' else '' end as 允許空,
          a.column_default as 默認值,
          column_comment as 字段說明
        from information_schema.columns A 
        order by ordinal_position 

達夢6：

        select 
          d.name as tname,
	        a.name as 字段名,
	        a.TYPE as 類型,
	        a.LENGTH as 長度,
	        a.SCALE as 小數位數,
	        case when a.NULLABLE = 'Y' then '√' else '' end as 允許空,
	        isnull(a.DEFVAL, '') as 默認值,
	        isnull(a.resvd5, '') as 字段說明
        from SYSDBA.SYSCOLUMNS a 
        inner join SYSDBA.SYSTABLES d on a.id = d.id and d.type = 'U'
        order by a.id, a.colid   

達夢7：

        select 
          a.table_name as tname,
	        a.column_name as 字段名, 
	        a.data_type as 類型,
	        a.data_length as 長度,
	        case when a.data_scale = null then 0 else a.data_scale end as 小數位數,
	        case when a.nullable = 'Y' then '√' else '' end as 允許空,
	        a.data_default as 默認值,
	        case when b.comments = null then '' else b.comments end as 字段說明
        from user_tab_columns A 
        left join user_col_comments B on A.table_name = B.table_name 
        and A.column_name = B.column_name 
        order by column_id      

人大金倉：

        select 
          a.table_name as tname,
	        a.column_name as 字段名, 
	        a.data_type as 類型,
	        a.data_length as 長度,
	        case when a.data_scale = null then 0 else a.data_scale end as 小數位數,
	        case when a.nullable = 'Y' then '√' else '' end as 允許空,
	        a.data_default as 默認值,
	        '' as 字段說明 --相關的字段說明沒有找到怎麼獲取
        from user_tab_columns A 
        order by column_id   

當前的數據庫設計更傾向於直接在數據庫里建數據字典表，就不存在數據庫兼容適配的問題了。