create table testtable(c1 int NOT NULL, c2 char(6) NOT NULL, c3 char(7) NOT NULL)
create INDEX IDX5 ON testtable (c2,c1,c3)select c.name,b.name,a.keyno from dbo.sysindexkeys a,syscolumns b,sysindexes c
where a.id=b.id and a.colid=b.colid and a.id=object_id('testtable') and a.indid=c.indid and c.name='idx5'
drop table testtable
create INDEX IDX5 ON testtable (c2,c1,c3)select c.name,b.name,a.keyno from dbo.sysindexkeys a,syscolumns b,sysindexes c
where a.id=b.id and a.colid=b.colid and a.id=object_id('testtable') and a.indid=c.indid and c.name='idx5'
drop table testtable
drop table testtable
create table testtable(c1 int NOT NULL, c2 char(6) NOT NULL, c3 char(7) NOT NULL)
create INDEX IDX5 ON testtable (c2,c1,c3)
然后:
select c.name,b.name,a.keyno from dbo.sysindexkeys a,syscolumns b,sysindexes c where a.id=b.id and a.colid=b.colid and a.id=object_id('testtable') and a.indid=c.indid and c.name='idx5'
得到这样的结果:
name name keyno
IDX5 c1 2
IDX5 c2 1
IDX5 c3 3你能解释下这些数字的意思吗?或者,我如何利用这些数字得到我想要的“一个index所涉及到的列的数目”?谢谢!
create table testtable(c1 int NOT NULL, c2 char(6) NOT NULL, c3 char(7) NOT NULL)
create INDEX IDX5 ON testtable (c2,c1,c3)
select c.name,列数目=count(b.name) from dbo.sysindexkeys a,syscolumns b,sysindexes c where a.id=b.id and a.colid=b.colid and a.id=object_id('testtable') and a.indid=c.indid and c.name='idx5' group by c.name
drop table testtable
create table testtable(c1 int NOT NULL, c2 char(6) NOT NULL, c3 char(7) NOT NULL)
create INDEX IDX5 ON testtable (c2,c1,c3)
select 列数目=count(b.name) from dbo.sysindexkeys a,syscolumns b,sysindexes c where a.id=b.id and a.colid=b.colid and a.id=object_id('testtable') and a.indid=c.indid and c.name='idx5'