December 8, 2005 at 7:45 am
How to get list of Indexes I have in my database.
1.Can I get list of all the cluster or Non-cluster indexes in db.
2. Can I get list of Primary ket in all the tables in the dB.
Thanks
Nita
December 8, 2005 at 8:10 am
If you search, you will find a couple of scripts on this site that will give that information to you. One of those that I find useful is (this answers your 3 questions and more):
/*Index Info*/
/*
This T-SQL code will provide information about the Indexes:
Table_Name, Index_Name and the name of the columns which constitute the index.
*/
-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @empty varchar(1)
select @empty = ''
declare @des1 varchar(35), -- 35 matches spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216
select o.name,
i.name,
'index description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (i.status & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (i.status & 1)<>0 then ', '+@des1 else @empty end
+ case when (i.status & 2)<>0 then ', '+@des2 else @empty end
+ case when (i.status & 4)<>0 then ', '+@des4 else @empty end
+ case when (i.status & 64)<>0 then ', '+@des64 else
case when (i.status & 32)<>0 then ', '+@des32 else @empty end end
+ case when (i.status & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (i.status & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (i.status & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (i.status & 16777216)<>0 then ', '+@des16777216 else @empty end),
'index column 1' = index_col(o.name,indid, 1),
'index column 2' = index_col(o.name,indid, 2),
'index column 3' = index_col(o.name,indid, 3),
'index column 4' = index_col(o.name,indid, 4),
'index column 5' = index_col(o.name,indid, 5),
'index column 6' = index_col(o.name,indid, 6),
'index column 7' = index_col(o.name,indid, 7),
s.groupname as FILE_GROUP
from sysindexes i, sysobjects o, sysfilegroups s
where i.id = o.id
and i.groupid = s.groupid
and indid > 0
and indid < 255
and o.type = 'U'
--exclude autostatistic index
and (i.status & 64) = 0
and (i.status & 8388608) = 0
and (i.status & 16777216)= 0
order by o.name
December 8, 2005 at 8:15 am
1)
get all clustered index, including the clustered index defined by PK and Unique constraint:
select b.name as tablename,a.* FROM sysindexes a, sysobjects b where a.id=b.id and b.xtype='U' and a.indid=1
Clustered index only:
select b.name as tablename,a.* FROM sysindexes a, sysobjects b where a.id=b.id and b.xtype='U' and a.indid=1
and not exists(select 1 from sysobjects c where a.name=c.name and c.xtype IN ('PK','UQ'))
Non clustered index not including PK and unique constraints (uncomment the part /* .. */ if you need them)
select b.name as tablename,a.* FROM sysindexes a, sysobjects b where a.id=b.id and b.xtype='U' and a.indid>1 AND a.indid<255 AND a.name not like '_WA_Sys_%'
/*
and not exists(select 1 from sysobjects c where a.name=c.name and c.xtype IN ('PK','UQ'))
*/
2) get a list of PK for all user tables in the db
SELECT b.name as TableName,a.* FROM sysobjects a,sysobjects b where a.xtype='PK' and a.parent_obj=b.id and b.xtype='U'
December 8, 2005 at 8:15 am
Thanks Sharma ji
Nita
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply