select name from syscolumns where id=object_id('表名') and colid in( select colid from sysindexkeys where object_id('表名')=id and indid in( select indid from sysindexes where object_id('表名')=id and name in( select name from sysobjects where xtype='PK' and parent_obj=object_id('表名') )))--根据主键查询表的列名
select i.name [索引名],o.name [所属表],case when i.indid=1 then 'CLUSTERED' else 'NONCLUSTERED' end [索引类型],c.name [列名] from dbo.sysindexes i, dbo.sysobjects o, dbo.syscolumns c,dbo.sysindexkeys k where i.id=o.id and o.type='U' and i.indid not in(0,255) and c.id=i.id and c.id=k.id and c.colid=k.colid and i.indid=k.indid 以上语句查出结果包括主键,加上下面语句可以去掉主键的信息 and i.name not in (select o1.name from dbo.sysindexes i,dbo.sysobjects o,dbo.sysobjects o1 where i.id=o.id and o.type='U' and i.indid not in(0,255) and o1.name=i.name and o1.xtype='PK')
2005比较方便了: select b.name as TableName ,a.* from sys.indexes a join sys.objects b on a.object_id = b.object_id where b.Type = 'U'
select colid from sysindexkeys where object_id('表名')=id and indid in(
select indid from sysindexes where object_id('表名')=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=object_id('表名')
)))--根据主键查询表的列名
以上语句查出结果包括主键,加上下面语句可以去掉主键的信息
and i.name not in (select o1.name from dbo.sysindexes i,dbo.sysobjects o,dbo.sysobjects o1 where i.id=o.id and o.type='U' and i.indid not in(0,255) and o1.name=i.name and o1.xtype='PK')
select b.name as TableName ,a.*
from sys.indexes a join sys.objects b
on a.object_id = b.object_id
where b.Type = 'U'