Finding the names of all indexes on a SQL Server

Finding the names of all indexes on a SQL Server
Download PDF

I needed a way to list the indexes for an entire database, but I was running into a problem using sys.indexes that I could only see the indexes for the current database. Below is the solution that I put together to list off of the indexes in the entire database.


CREATE TABLE ##allindexes
(
databse_id INTEGER,
table_id INTEGER,
index_id INTEGER,
table_name      VARCHAR(1024)
);
EXECUTE Sp_msforeachdb
'use [?];
INSERT INTO ##AllIndexes
select DB_ID() as database_id, object_id as table_id, index_id, name
from sys.indexes
where name is not null'

SELECT *
FROM   ##allindexes;
DROP TABLE ##allindexes;

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

2 Comments on “Finding the names of all indexes on a SQL Server

  1. In the current database context you will see all indexes for that database in sys.indexes.
    If you want indexes for the entire instance then collating results from this catalog in all dbs will provide the results required

  2. Steve, did you perhaps mean to say “server” or “instance” rather than “database” , when you refer here to “an entire database” and “the entire database”? It would seem so, from the title, the rest of the post, and the code. Sorry if I’ve somehow misinterpreted things.

Leave a Reply

Your email address will not be published. Required fields are marked *

*