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:
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!
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
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.