如果通过T-SQL代码获取表中字段信息

MSSQL2000：
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.,
=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
=case when a.isnullable=1 then '√'else '' end,
=isnull(e.text,''),
=isnull(g.,'')
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'
inner join sys.objects s on d.id=s.
inner join sys.schemas sch on s.=sch.
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
where d.name='ApplyInfo' --如果只查询指定表,加上此条件
and sch.name='check' --如果表名重复，需要通过架构名来区别
order by a.id,a.colorder


MSSQL2008及以上（因为没有2005版本，无法验证）：
SELECT A.name AS ,
B.name AS ,
C.column_id AS ,
C.name AS ,
CASE WHEN C.is_identity=1 THEN '√' ELSE '' END AS ,
CASE WHEN EXISTS (
SELECT TOP (1) *
FROM sys.objects AS A1
WHERE A1.parent_object_id=C.
AND A1.=N'PK'
AND A1.name IN (
SELECT name
FROM sys.indexes
WHERE index_id IN (
SELECT index_id
FROM sys.index_columns AS B1
WHERE B1.column_id=C.column_id
AND B1.=C.
)
)
) THEN '√' ELSE '' END AS ,
D.name AS ,
C.max_length AS ,
C. AS ,
C.scale AS ,
CASE WHEN C.is_nullable=1 THEN '√' ELSE '' END AS ,
ISNULL(E.,'') AS ,
ISNULL(F.value,'') AS
FROM sys.schemas AS A INNER JOIN
sys.tables AS B ON A.=B. INNER JOIN
sys.columns AS C ON B.=C. INNER JOIN
sys.types AS D ON C.user_type_id=D.user_type_id LEFT JOIN
sys.default_constraints AS E ON C.default_object_id=E. LEFT JOIN
sys.extended_properties AS F ON C.object_id=F.major_id
AND C.column_id=F.minor_id
WHERE A.name='架构名'
AND B.name='表名'
ORDER BY C.column_id