数据字典可以帮助开发人员理解数据项的类型、值以及它们与现实世界中对象的关系。数据字典是数据库设计中不可缺少的一部分。本文列出了从几种常用数据的相关现有表中获取数据字典的一些脚本。以下脚本仅测试一些数据库版本，可能并不都适用。
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.***allid 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.***jor_id and a.colid = g.minor_id order by a.id, a.colorder
甲骨文:
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_***ximum_length as 长度, a.numeric_scale as 小数位数, case when a.is_nullable = 'YES' then '√' else '' end as 允许空, a.column_default as 默认值, column_comment as 字段说明 from infor***tion_sche***.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
现在的数据库设计更倾向于直接在数据库中建立数据字典表，所以不存在数据库兼容和适配的问题。
本文来自永远太远投稿，不代表舒华文档立场，如若转载，请注明出处：https://www.chinashuhua.cn/24/480431.html
微信扫一扫 
