（转）sqlserver2005 读取数据库所有表及字段

/*

1.获取所有数据库名:

Select Name FROM Master..SysDatabases ORDER BY Name

2.获取所有表名:

Select Name FROM DatabaseName..SysObjects Where XType='U' ORDER BY Name

XType='U':表示所有用户表;

XType='S':表示所有系统表;

3.获取所有字段名:

Select Name FROM SysColumns Where id=Object_Id('TableName')

*/

?

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,'PRECISION '),?

小数位数?????=??ISNULL(COLUMNPROPERTY(A.ID,'SCALE '),0),?

允许空?????????=??CASE??WHEN??A.ISNULLABLE=1??THEN??'√'ELSE??' '??END,?

缺省值?????????=??ISNULL(E.TEXT,' '),?

字段说明?????=??ISNULL(G.[VALUE],' ')?

FROM???

SYSCOLUMNS??A?

LEFT??JOINSYSTYPES??B???

???ON?A.XUSERTYPE=B.XUSERTYPE?

INNER??JOIN??SYSOBJECTS??D???

???ON?A.ID=D.ID????AND??D.XTYPE='U '??AND????D.NAME<>'DTPROPERTIES '?

LEFT??JOINSYSCOMMENTS??E???

???ON?A.CDEFAULT=E.ID?

LEFT??JOINsys.extended_properties??G???

???ON?A.ID=G.major_id??AND??A.COLID=G.minor_id?????

LEFT??JOINsys.extended_properties??F???

???ON?D.ID=F.major_id??AND??F.minor_id=0

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!