Reprint SQL Get database all tables and their field names, types, lengths

Reprint Original Address

Http://www.cnblogs.com/Fooo/archive/2009/08/27/1554769.html

SELECT (case if A.colorder=1 then d.name Else ' end) Table name, A.colorder field ordinal, a.name field name, when Columnpro                  Perty (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 Sysind                                                                                                  Exkeys 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 ' primary key, B.name type, A.length occupies bytes, columnproperty (a.id,a.name, ' PRECISION ')    As length, IsNull (ColumnProperty (a.id,a.name, ' scale '), 0) as decimal place, (case time a.isnullable=1 then ' √ ' else ' end) allows null, IsNull (E.text, ') default value, IsNull (G.[value], ') as field description from syscolumns a LEFT join systypes B on A.xtype=b.xuser  Type 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--where d.name= "Child" ORDER by A.I D,a.colorder

Reprint SQL Get database all tables and their field names, types, lengths