当前位置:Gxlcms > mysql > [生成数据字典]取数据库表中字段的描述信息

[生成数据字典]取数据库表中字段的描述信息

时间:2021-07-01 10:21:17 帮助过:13人阅读

Sql2000: ========================== SELECT SysObjects.Name, -- 表名 SysColumns.Name, -- 字段名 SysTypes.Name, -- 字段类型 SysColumns.Length, -- 字段长度 SysProperties.Value -- 字段描述 FROM SysObjects, SysTypes, SysColumns LEFT JOIN SysPro

Sql2000:
==========================

SELECT SysObjects.Name, --表名
SysColumns.Name, --字段名
SysTypes.Name, --字段类型
SysColumns.Length, --字段长度
SysProperties.Value --字段描述
FROM SysObjects,
SysTypes,
SysColumns
LEFT JOIN
SysProperties
ON (Syscolumns.Id = Sysproperties.Id AND Syscolumns.Colid =
Sysproperties.Smallid)
WHERE (Sysobjects.Xtype ='u' OR Sysobjects.Xtype ='v'
)
AND Sysobjects.Id =
Syscolumns.Id
AND SysTypes.XType =
Syscolumns.XType
AND Sysobjects.Name = 'Contract' --指字要查找的表名

ORDER BY smallid




SqlServer2005:
=============================

SELECT
表名
= case when a.colorder=1 then d.name else '' end
,
表说明
= case when a.colorder=1 then isnull(f.value,'') else '' end
,
字段序号
=
a.colorder,
字段名
=
a.name,
标识
= case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then ''else '' end
,
主键
= case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in
(
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '' else '' end
,
类型
=
b.name,
占用字节数
=
a.length,
长度
= 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.xusertype
=
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
left join


sys.extended_properties f
on

d.id
=f.major_id and f.minor_id=0

where
d.name
='Contract' --如果只查询指定表,加上此条件

order by
a.id,a.colorder

人气教程排行