ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

[MSSQL] Get Indexes Info

Updated on April 28, 2011

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

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article