获取SQLSERVER一张表里面的常用字段信息

SELECT
TableName=d.name,--表名称
FieldNo=a.colorder,--字段编号
FieldName=a.name,--字段名称
IsIdentity=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then 'true'else 'false' end,--是否自增
PrimaryKey=case when exists
(
     SELECT 1 FROM sysobjects where xtype='PK' and name in
     (
         SELECT name FROM sysindexes WHERE indid in
         (
              SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
         )
     )
)
then 'true' else 'false' end,--是否主键
FieldType=b.name,--字段类型
FieldLength=a.length,--字段长度
FieldSize=COLUMNPROPERTY(a.id,a.name,'PRECISION'),--字段大小
FieldScale=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),--小数位数
FieldAllowNull=case when a.isnullable=1 then 'true' else 'false' end,--是否为空
DefaultValue=isnull(e.text,''),--默认值
FieldRemark=isnull(g.[value],'')--字段说明
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 join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id--SQL2005
--left join sysproperties g on a.id=g.id and a.colid=g.smallid
where d.name='TableName'
order by a.id,a.colorder