怎么导出SQL所有用户表的字段信息。（２中方法实现）

来源：互联网发布：linux sudo chown r 编辑：程序博客网时间：2024/06/06 02:11

以下几个地址都是能所有用户表的字段信息。

http://edu.itbulo.com/200609/107074.htm

http://www.studynew.com/study/Mssql/2004070505500719723.html

http://www.oioq.com/Article/data/2006/0821/article_9831.html

http://topic.csdn.net/t/20031111/16/2450021.html

http://www.ntc.com.cn/MsSQL/MsSQL_34006.htm

方法一：

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

方法二：

任何数据库中，查看这个数据库中的各个表的结构：（按照各个表的列排列）
select t1.tablename,t1.colname,descr=isnull(t2.value,''),t1.type,t1.length,t1.isnullable

from

(select a.id,tablename=d.name,colname=a.name ,colid=a.colid,type=b.name ,a.length, a.isnullable

from syscolumns a, systypes b,sysobjects d

where a.xtype=b.xusertype and a.id=d.id and d.xtype='U' )

t1 left join sysproperties t2 on t1.id=t2.id and t1.colid=t2.smallid