[MSSQL] Get Indexes Info

Sometimes while writing queries (and optimizing them), you have to check the indexes associated to the corresponding tables.

A good way to accomplish that is with the system stored procedure called sp_helpindex.  It is quite simple procedure: EXEC sp_helpindex [@objname =] 'name' , where name  refers to the table you want to retrieve indexes info.

The output goes like this:

index_name
index_description
index_keys
F4211_1
nonclustered, unique located on PRIMARY
SDDOCO, SDDCTO, SDKCOO, SDLNID
F4211_2
nonclustered located on PRIMARY
SDDCTO, SDNXTR, SDLNTY
F4211_3
nonclustered located on PRIMARY
SDAN8
F4211_4
nonclustered located on PRIMARY
SDSHAN

Another way to get main info about indexes is through a query on system tables.

Of course, you can change the following example to whatever you need to achieve, but I think it fits most of the needs:

 
SELECT 
	OBJECT_NAME(I.OBJECT_ID) AS TABLE_NAME, 
	I.NAME AS INDEX_NAME, 
	C.NAME AS COLUMN_NAME,
	I.TYPE_DESC, 
	IC.IS_DESCENDING_KEY, 
	IC.KEY_ORDINAL
FROM SYS.INDEXES I
JOIN SYS.INDEX_COLUMNS IC ON (IC.OBJECT_ID = I.OBJECT_ID AND IC.INDEX_ID = I.INDEX_ID)
JOIN SYS.COLUMNS C ON (C.OBJECT_ID = IC.OBJECT_ID AND C.COLUMN_ID = IC.COLUMN_ID)
WHERE OBJECT_NAME(I.OBJECT_ID) = 'name'
ORDER BY I.TYPE_DESC, I.NAME, IC.KEY_ORDINAL


There are also other fields in each table and you're free to use them.

Bye!

Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working