数据字典实例详解部分数据库版本已在文中进行测试

来源：互联网　2023-02-08 15:25:19

(资料图片)

数据字典可以帮助开发人员理解各个数据项目的类型、数值和它们与现实世界中的对象的关系。做数据库设计时数据字典是不可或缺的一部分，本文列出了几种常用数据的相关已有表获取数据字典的一些脚本，以下脚本仅仅测试了部分数据库版本，未必全部适配。

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

当前的数据库设计更倾向于直接在数据库里建数据字典表，就不存在数据库兼容适配的问题了。

推荐DIY文章