汇出数据表字段(&属性)清单

以下语句可将SQL数据库中，所有的表及字段(属性：表说明、字段名、字段类型、字段顺序、长度、是否为空、字段说明等)，汇出至一个临时表。以备用。

 

Select 1 as Type,A.Name as tName,'表名' as Col1,'中文标题' as Col2,'说明' as Col3,

'' as Col4,'' as Col5,'' as Col6,'' as Col7,'' as Col8,'' as Col9 Into #t

From SysObjects A Where A.xtype='U'

Union

Select 2 as Type,A.Name as tName,A.Name as Col1,null as Col2,C.Value as Col3,

'' as Col4,'' as Col5,'' as Col6,'' as Col7,'' as Col8,'' as Col9

From SysObjects A Left join sysproperties C on A.id=C.id and C.smallid=0 and C.Type=3

Where A.xtype='U'

Union

Select 3 as Type,A.Name as tName,'字段' as Col1,'中文标题' as Col2,'顺序' as Col3,

'字段类型' as Col4,'字段长度' as Col5,'允许为空' as Col6,'字段说明' as Col7,'自动增量' as Col8,

'主键' as Col9

From SysObjects A Where A.xtype='U'

Union

Select 4 as Type,A.Name as tName,B.Name as Col1,'' as Col2,Convert(Nvarchar,B.ColOrder) as Col3,

D.Name+Case When D.Name in ('Numeric','Decimal') then '('+Convert(Nvarchar,B.xprec)+','+Convert(Nvarchar,B.xscale)+')'

else '' end as Col4,Convert(Nvarchar,B.Length) as Col5,

Case When B.isnullable=1 then '是' else '否' end as Col6,

C.Value as Col7,Case When B.autoval is not null then '是' else '' end as Col8,

Case When S.tName is null then '' else '是' end as Col9

From SysObjects A Inner join SysColumns B on A.id=b.id

Left join sysproperties C on A.id=C.id and B.colid=C.smallid and C.type=4

Left join systypes D on B.xtype=D.xtype and B.xUserType=D.xusertype

Left join (Select A.Name as tName,B.Name as ColName

From SysObjects A Inner join SysColumns B on A.id=b.id

Inner join SysObjects D on A.id=D.Parent_Obj and D.Xtype='PK'

Inner join sysindexes C on A.id=C.id and D.Name=C.Name

Inner join sysindexkeys S on C.id=S.id and C.indid=S.indid and B.colid=S.colid

Where A.Xtype='U') S on A.Name=S.tName and B.Name=S.ColName

Where A.xtype='U'

Order By tName,Type,Col3

 

Select Type,tName,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9 From #t Order By tName,Type,Case When Type=4 then Convert(int,Col3) else 0 End

 

drop table #t