Query display MSSQL table structure [GO]

SELECT Table name= Case when a.colorder=1Then D.name Else"'End, table description= Case when a.colorder=1Then IsNull (F.value,"') Else"'End, field ordinal=a.colorder, field name=a.name, field description= IsNull (G.[value],"'), identify= case when ColumnProperty (A.id,a.name,'isidentity')=1Then'√'Else"'End, PRIMARY key= case when exists (SELECT1From sysobjects Where xtype='PK'and Parent_obj=a.id and nameinch(SELECT name from sysindexes WHERE indidinch(SELECT indid from Sysindexkeys WHERE id = a.id and colid=a.colid))) Then'√' Else "'End, type=b.name, number of bytes occupied=a.length, Length= ColumnProperty (A.id,a.name,'PRECISION'), number of decimal digits= IsNull (ColumnProperty (A.id,a.name,' Scale'),0), allow null= Case when a.isnullable=1Then'√'Else"'End, default value= IsNull (E.text,"') from syscolumns A left Join systypes B on A.xusertype=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 sys.extended_properties G on a.ID=G.MAJOR_ID and A.colid=g.minor_id left Join sys.extended_properties F on D.id=F.MAJOR_ID and F.minor_id=0--whereD.name='OrderInfo'--If you query only the specified table, add this condition to Order by A.id,a.colorder

Reference: http://www.cnblogs.com/LeeYongze/archive/2012/07/19/2599338.html

SELECT Table name = CaseWhen a.colorder=1Then D.nameElse "'End, table description= CaseWhen a.colorder=1Then IsNull (F.value,"')Else "'End, field ordinal=a.colorder, field name=a.name, Logo= CaseWhen ColumnProperty (A.id,a.name,'isidentity')=1Then'√'Else "'end, primary key= CaseWhen exists (SELECT1From sysobjectswhereXtype='PK'and Parent_obj=a.id and nameinch(SELECT name from sysindexes WHERE indidinch(SELECT indid from Sysindexkeys WHERE id = a.id and colid=a.colid))) Then'√' Else "'End, type=b.name, number of bytes occupied=a.length, Length= ColumnProperty (A.id,a.name,'PRECISION'), number of decimal digits= IsNull (ColumnProperty (A.id,a.name,' Scale'),0), allow null= CaseWhen a.isnullable=1Then'√'Else "'end, default value= IsNull (E.text,"'), field description= IsNull (G.[value],"') from syscolumns a LEFT join systypes B on A.xusertype=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 Sys.extended_properties G on a.id=G.MAJOR_ID and A.colid=g.minor_id left join Sys.extended_properties F on d.id=F.MAJOR_ID and F.minor_id=0      whereD.name='shipped_list'--If you query only the specified table, plus this condition, the table name order by A.id,a.colorder--======================================================================================Selecta.name column name, c.name data type, a.max_length length, a.is_nullable is empty fromsys.columns A, sys.tables B, sys.types cwhereA.object_id=b.object_id and a.system_type_id=c.system_type_id and B.name='Inv_master'--If you query only the specified table, plus this condition, the table name order by a.column_id

http://blog.csdn.net/hcf_force/article/details/7666003

Query display MSSQL table structure [GO]