首页 > 代码库 > SQLServer2005,2000获取表结构:字段名、类型、长度、主键、非空、注释

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

SQLServer 2005

SELECT    d.name NTableName,    d.xtype NTableType,    a.colorder NColumnIndex,    a.name NColumnName,    (case when COLUMNPROPERTY( a.id,a.name,IsIdentity)=1 then 1else 0 end) NIdnetityFG,    (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    ) NIsPK,    b.name NDataType,    COLUMNPROPERTY(a.id,a.name,PRECISION) as NLength,    (case when a.isnullable=1 then 1else 0 end) NIsNullable,    isnull(e.text,‘‘) NDefault,    g.value NDescription    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 NTableName,    d.xtype NTableType,    a.colorder NColumnIndex,    a.name NColumnName,    (case when COLUMNPROPERTY( a.id,a.name,IsIdentity)=1 then 1else 0 end) NIdnetityFG,    (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    ) NIsPK,    b.name NDataType,    COLUMNPROPERTY(a.id,a.name,PRECISION) as NLength,    (case when a.isnullable=1 then 1else 0 end) NIsNullable,    isnull(e.text,‘‘) NDefault,    g.value NDescription    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