日期:2014-05-17 浏览次数:20576 次
SELECT
字段序号=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 name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then ' 主键字段 ' else ' ' end
FROM syscolumns a
where id=object_id(N 'jobs ') --要查询的表名
and( --标识字段
COLUMNPROPERTY( a.id,a.name, 'IsIdentity ')=1
or --主键字段
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
))))
order by a.id,a.colorder
SELECT
表名=case when a.colorder=1 then d.name 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 name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND &n