Identifying indexes that use same columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Identifying indexes that use same columns?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-07-22 : 02:25:38

Is there a quick-n-easy way to find out which indexes on a table use the same column?

i.e. say i have a column 'id' in the table 'table1' and i want to know the names of all the indexes that use that column (listing all the other columns used in those indexes as well) how would I do it?

I want to do this so I can (possibly) remove 'redundant' indexes if the same columns are used elsewhere.

cheers.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 02:57:08

[code]

select object_name(ic.object_id) [table], c.name [column]
FROM sys.index_columns ic
join sys.indexes i
on ic.object_id = i.object_id
And ic.index_id = i.index_id
JOIN sys.columns c
On c.object_id = i.object_id
And c.column_id = ic.index_column_id
JOIN sys.tables t
On t.object_id = c.object_id
GROUP BY
ic.object_id, c.name
HAVING COUNT(i.index_id) > 1
[/code]

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-07-22 : 03:25:57

Thanks for that,
I now got to build on it to get the whole listing of the identified indexes to know what other columns are included in them.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-07-22 : 05:04:40

OK, so far modifying the above statement I get:-


select	object_name(ic.object_id) [table],i.NAME [index name],COUNT(i.index_id) [column duplicated]
FROM	sys.index_columns ic
join	sys.indexes i
on	ic.object_id = i.object_id
And	ic.index_id = i.index_id
JOIN	sys.columns c
On	c.object_id = i.object_id
And	c.column_id = ic.index_column_id
JOIN	sys.tables t
On	t.object_id = c.OBJECT_ID
GROUP BY
	ic.object_id,i.name
HAVING COUNT(i.index_id) > 1

Is there now a way of listing all the columns used in the indexes above?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources