Jul 9

Written by: Steve Gray
7/9/2010 3:48 PM 

So, I’m working on a query at a client, and my query runs against a table that I know has a few indexes on it. I’m not the data architect, I’m just trying to get this query to run a little faster.

I want to see (quickly) what indexes already exist on this table, so…

This script will do that. It runs against one table and shows all the indexes. The end result looks like this:

Now I can tell what I need to do to speed up this query.

 

select i.name, convert(varchar(2) ,i.index_id) as index_id ,'' as columnName, i.name as name3, 0 as key_ordinal
    into #tmp
    from sys.indexes i
    join sys.objects o on o.object_id = i.object_id 
    where o.name = 'gl20000' 
union all
select '', '' ,c.name, i.name, ic.key_ordinal
    from sys.indexes i 
    join sys.objects o on o.object_id = i.object_id 
    join sys.index_columns ic on ic.index_id = i.index_id and o.object_id = ic.object_id 
    join sys.columns c on c.column_id = ic.column_id and c.object_id = o.object_id 
    where o.name = 'gl20000' 
    order by i.name, key_ordinal
select name, index_id, columnName
    from #tmp
    order by name3, key_ordinal
drop table #tmp