/*
查询标识字段或主键字段 [根据表名查询]
*./ SELECT 字段序号=a.colorder, 字段名=a.name, 字段类型=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '标识字段'else '主键字段' end FROM syscolumns a where id=object_id(N'loginuser') --要查询的表名 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.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 inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' order by a.id,a.colorder