数据字典实例详解(做一个简单的网页查询数据库)

   发布日期:2022-07-18 09:58:36     手机:https://m.haocat.cn/yule/news5294.html    违规举报
核心提示:数据字典可以帮助开发人员理解各个数据项目的类型、数值和它们与现实世界中的对象的关系。做数据库设计时数据字典是不可或缺的一部分,本文列出了几种常用数据的相关已有表获取数据字典的一些脚本,以下脚本仅仅测试了部分数据库版本,未必全部适配。 Sql

数据字典实例详解(做一个简单的网页查询数据库)

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

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

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

 
 
本文地址:http://haocat.cn/yule/news5294.html,转载请注明出处。
 
更多>同类娱乐运动

推荐图文
推荐娱乐运动
点击排行
网站首页  |  网站流量  |  联系方式  |  使用协议  |  版权隐私  |  网站地图  |  违规举报  |  SiteMaps  |  BaiDuNews