列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息 -- 风信Java论坛 -- Powered by JavaWind.Net

唧唧

大小楼主列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息 Post by : 2010-11-16 15:51:20.0

幼儿园
UID：3
主题：342
帖子：781
加为好友发送短信

--列出sql server(WINDOWS平台上强大的数据库平台) 所有表，字段名，主键，类型，长度，小数位数等信息

--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中

运行复制保存 sql代码

select
( case when a.colorder = 1 then d.name else '' end ) 表名,
a.colorder 字段序号,
a.name 字段名,
( case when COLUMNPROPERTY (a.id,a.name,'isidentity') = 1 then '√' else '' end ) 标识,
( case when (
select count(*) from sysobjects
where name in (
select name from sysindexes
where (id = a.id ) and ( indid in
(select indid from sysindexkeys where
( id = a.id ) and ( colid in (
select colid from syscolumns
where ( id = a.id ) and ( name = a.name ))))))
and ( xtype ='PK')) > 0 then '√' else '' end ) 主键,
b.name 类型,
a.length 字节数,
COLUMNPROPERTY ( a.id,a.name ,'PRECISION' ) as 长度,
isnull ( COLUMNPROPERTY ( a.id,a.name ,'Scale'),0) as 小数位数,
(case when a.isnullable = 1 then '√' else '' end ) 允许空,
isnull ( e.text,'') 默认值,
isnull (g.[value],'' ) as 字段说明
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

　★★★★★★★★
　纵里寻她千百度，蓦然回首，那人却在，灯火阑珊处！
　MyBlog ：http://blog.javawind.net

唧唧精华：0 积分：0 金钱：3172 金币：0 威望：0 权限：2 用户资料用户帖子	大小楼主列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息 Post by : 2010-11-16 15:51:20.0
幼儿园 UID：3 主题：342 帖子：781 加为好友发送短信	--列出sql server(WINDOWS平台上强大的数据库平台) 所有表，字段名，主键，类型，长度，小数位数等信息 --在查询分析器里运行即可,可以生成一个表，导出到EXCEL中运行复制保存 sql代码 select ( case when a.colorder = 1 then d.name else '' end ) 表名, a.colorder 字段序号, a.name 字段名, ( case when COLUMNPROPERTY (a.id,a.name,'isidentity') = 1 then '√' else '' end ) 标识, ( case when ( select count(*) from sysobjects where name in ( select name from sysindexes where (id = a.id ) and ( indid in (select indid from sysindexkeys where ( id = a.id ) and ( colid in ( select colid from syscolumns where ( id = a.id ) and ( name = a.name )))))) and ( xtype ='PK')) > 0 then '√' else '' end ) 主键, b.name 类型, a.length 字节数, COLUMNPROPERTY ( a.id,a.name ,'PRECISION' ) as 长度, isnull ( COLUMNPROPERTY ( a.id,a.name ,'Scale'),0) as 小数位数, (case when a.isnullable = 1 then '√' else '' end ) 允许空, isnull ( e.text,'') 默认值, isnull (g.[value],'' ) as 字段说明 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
	★★★★★★★★ 　纵里寻她千百度，蓦然回首，那人却在，灯火阑珊处！　MyBlog ：http://blog.javawind.net