I needed a way to list of of 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!