query to find the indexed columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query to find the indexed columns

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-30 : 05:10:26

Dear Experts, i need one query to find all the indexed columns with table names ,column names and type of indexes....

i'm trying with sysindexes, but i was unable to finish it....i'm not clear about keys column in sysindexes...

please guide me.

thank you very much

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 05:13:44

sp_helpindex <your table name>

will give what are the index and type of index and column name.

--------------------------------------------------
S.Ahamed

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-30 : 05:43:53

actually I need all the indexes in one database.....
not in one perticular table

Vinod
Even you learn 1%, Learn it with 100% confidence.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 06:59:44

Try this and check using for sp_helptext for some table whether ur getting correct result...

Select * from
(select object_name(si.id) [Object],index_col(object_name(si.id), si.indid, sk.keyno) [Column_Name]
from sysindexes si join sysindexkeys sk
on si.id = sk.id and si.indid = sk.indid and si.indid between 1 and 254 and (si.status & 64)=0 ) as b
where Column_Name is not null

--------------------------------------------------
S.Ahamed

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-30 : 07:06:46

this is greatly working but i was unable to get the name of the index....

thank you PBGUY

Vinod
Even you learn 1%, Learn it with 100% confidence.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 07:10:46

Added index name..please check...
Can others suggest whether the index name is correct

Select * from
(select object_name(si.id) [Object],index_col(object_name(si.id), si.indid, sk.keyno) [Column_Name],
case when (si.status & 16)<> 0 then 'clustered'
else 'nonclustered' end [Index Type]
from sysindexes si join sysindexkeys sk
on si.id = sk.id and si.indid = sk.indid and si.indid between 1 and 254 and (si.status & 64)=0 ) as b
where Column_Name is not null

--------------------------------------------------
S.Ahamed

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-05-30 : 07:26:50

This is absolutely great query PBGUY...............but please explan me the logic please

thank you very much

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-30 : 07:45:24

quote:
Originally posted by pbguy

Added index name..please check...
Can others suggest whether the index name is correct

Select * from
(select object_name(si.id) [Object],index_col(object_name(si.id), si.indid, sk.keyno) [Column_Name],
case when (si.status & 16)<> 0 then 'clustered'
else 'nonclustered' end [Index Type]
from sysindexes si join sysindexkeys sk
on si.id = sk.id and si.indid = sk.indid and si.indid between 1 and 254 and (si.status & 64)=0 ) as b
where Column_Name is not null

--------------------------------------------------
S.Ahamed

This is not going to give you the name of the index...only the name of the underlying table.

Try this instead:

Select * from 
	(
	select 
		si.name [Index Name], 
		object_name(si.id) [Object],
		index_col(object_name(si.id), si.indid, sk.keyno) [Column_Name], 
		case 
			when (si.status & 16)<> 0 then 'clustered' 
			else 'nonclustered' 
		end [Index Type]
	from sysindexes si join sysindexkeys sk 
	on si.id = sk.id and si.indid = sk.indid and si.indid between 1 and 254 and (si.status & 64)=0 
	) as b
where Column_Name is not null

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-06-01 : 04:32:11

Harsh, what is this statement...when (si.status & 16)<> 0 then 'clustered'
how is this working, please explain me....

Vinod
Even you learn 1%, Learn it with 100% confidence.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 05:18:00

Hi Vinod,

When u asked about the type of index, i saw the code written inside the sp_helpindex system procedure, there i found this code. May be for their convinient they are using this column to find the index type..

This column is undocumented...if you are not relying on this column since it is undocumented, please use indexproperty sysfunction to find the index type..

I found in some article that instead of using status column it is better to use indexproperty system function..

Select * from
( select si.name [Index Name],
object_name(si.id) [Object],
index_col(object_name(si.id), si.indid, sk.keyno) [Column_Name],
case when indexproperty(si.id, si.name, 'IsClustered') = 1 then 'Cluster'
else 'nonclustered' end [Index Type]
from sysindexes si join sysindexkeys sk
on si.id = sk.id and si.indid = sk.indid and si.indid between 1 and 254 and (si.status & 64)=0 ) as b
where Column_Name is not null

--------------------------------------------------
S.Ahamed

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-06-05 : 06:12:04

Dear Pbguy,
how can i see the inside code of this sp_helpindex ?

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-05 : 06:30:21

How do you view text for any SP? It's same for sp_helpindex.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-05 : 06:34:42

Use master
Go
sp_helptext sp_helpindex

--------------------------------------------------
S.Ahamed