SQLServer2005,2000获取表结构：字段名、类型、长度、主键、非空、注释

SQLServer 2005

SELECT    d.name N'TableName',    d.xtype N'TableType',    a.colorder N'ColumnIndex',    a.name N'ColumnName',    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IdnetityFG',    (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    ) N'IsPK',    b.name N'DataType',    COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'Length',    (case when a.isnullable=1 then '1'else '0' end) N'IsNullable',    isnull(e.text,'') N'Default',    g.value N'Description'    FROM   syscolumns   a     left join systypes b on   a.xtype=b.xusertype    inner join sysobjects d on a.id=d.id   and   d.xtype in ('U','V') and   d.name<>'dtproperties'    left join syscomments e on a.cdefault=e.id    LEFT OUTER JOIN sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_idorder by object_name(a.id),a.colorder

SQLServer2000

SELECT    d.name N'TableName',    d.xtype N'TableType',    a.colorder N'ColumnIndex',    a.name N'ColumnName',    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IdnetityFG',    (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    ) N'IsPK',    b.name N'DataType',    COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'Length',    (case when a.isnullable=1 then '1'else '0' end) N'IsNullable',    isnull(e.text,'') N'Default',    g.value N'Description'    FROM   syscolumns   a     left join systypes b on   a.xtype=b.xusertype    inner join sysobjects d on a.id=d.id   and   d.xtype in ('U','V') 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.smallidorder by object_name(a.id),a.colorder--select * from sysproperties