- HubPages»
- Technology»
- Computers & Software»
- Computer Software
[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!