SQL: Get All Tables and All Indexes from All Databases
Author: jason
Date: 2014-04-13 17:48:14
Category: Technical

Use the sp_MSforeachdb procedure to go into each database and select from the sys.tables and sys.indexes system tables into a Temp Table.





Code

CREATE TABLE #ListIndexes (
[DB_NAME] NVARCHAR(128), [DB_ID] INT
, Table_Name NVARCHAR(128), [Object_ID] INT
, [Schema_Name] NVARCHAR(128), [Schema_ID] INT
, Index_Name NVARCHAR(128), Index_ID INT
, Type_Desc NVARCHAR(60)
)

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #ListIndexes
SELECT DB_NAME(DB_ID()) DB_Name, DB_ID() [DB_ID]
, t.name Table_Name, t.[Object_ID]
, Schema_Name(t.[Schema_ID]) [Schema_Name], t.[Schema_ID]
, i.name Index_Name, i.Index_ID, i.Type_Desc
FROM sys.tables t
LEFT JOIN sys.indexes i ON i.[object_id] = t.[object_id]
';
SELECT * FROM #ListIndexes ORDER BY [DB_ID] DESC
DROP TABLE #ListIndexes


sys.tables
http://technet.microsoft.com/en-us/library/ms187406.aspx

sys.indexes
http://technet.microsoft.com/en-us/library/ms173760.aspx

sys.objects
http://technet.microsoft.com/en-us/library/ms190324.aspx

SYSNAME
http://technet.microsoft.com/en-us/library/ms191240(v=sql.105).aspx

SCHEMA_ID
http://technet.microsoft.com/en-us/library/ms188797.aspx



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009