sqlserver2005+ Getting table structure information

SELECT D.name TableName,
A.colorder Fieldno,a.name FieldName,
(Case if ColumnProperty (a.id,a.name, ' isidentity ') =1 then ' 1 ' else ' 0 ' end) Isidentity,
(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 ' 1 ' else ' 0 ' end) IsPrimaryKey,
B.name as FieldType,
A.length as Charlength,
ColumnProperty (a.id,a.name, ' PRECISION ') as Fieldlength,
IsNull (ColumnProperty (a.id,a.name, ' scale '), 0) as Decimaldigits,
(case is a.isnullable=1 then ' 1 ' else ' 0 ' end) IsNull,
IsNull (G.[value], ') as Description,
IsNull (E.text, ') defaultvalue

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 joins Sys.extended_properties G on a.id=g.major_id and a.colid = g.minor_id
where d.name = ' UserInfo '
ORDER BY A.id,a.colorder

sqlserver2005+ Getting table structure information