List index information for all databases

--****************************************************************************************
-- List index information for all databases
--****************************************************************************************
-- Version: 1.0
-- Author:Theo Ekelmans 
-- Email:theo@ekelmans.com
-- Date:2005-10-07
--****************************************************************************************

use master 

DECLARE @db_name varchar(128)
DECLARE @DbID int
DECLARE @sql_string nvarchar(4000)

set nocount on

CREATE TABLE [#tblHistoryIndex] (
[DbName] [varchar] (128) NOT NULL ,
[TableName] [varchar] (128) NOT NULL ,
[IndexName] [varchar] (128) NOT NULL ,
[Indexid] [int] NOT NULL ,
[Primary] [int] NULL ,
[Clustered] [int] NULL ,
[Unique] [int] NULL ,
[IgnoreDupKey] [int] NULL ,
[IgnoreDupRow] [int] NULL ,
[NoRecompute] [int] NULL ,
[FillFactor] [int] NULL ,
[EstRowCount] [bigint] NULL ,
[ReservedKB] [bigint] NULL ,
[UsedKB] [bigint] NULL ,
[KeyNumber] [int] NULL ,
[ColumnName] [varchar] (128) NULL ,
[DataType] [varchar] (128) NULL ,
[Precision] [int] NULL ,
[Scale] [int] NULL ,
[IsComputed] [int] NULL ,
[IsNullable] [int] NULL ,
[Collation] [varchar] (128) NULL ) 

declare db_cursor cursor forward_only for

SELECT name, DbID 
FROM master..sysdatabases
WHERE name NOT IN ('northwind', 'pubs')
AND (status & 32) <> 32     --loading.
AND(status & 64) <> 64    --pre recovery.
AND(status & 128) <> 128      --recovering.
AND(status & 256) <> 256      --not recovered.
AND(status & 512) <> 512    --Offline
AND(status & 32768) <> 32768  --emergency mode.
AND DbID > 4

open db_cursor

fetch next from db_cursor into @db_name, @DbID


while @@FETCH_STATUS = 0
begin

set @sql_string = ''
+'Insert into #tblHistoryIndex '
+'select ''' + @db_name + ''' as ''DbName'',  '
+'       o.name as ''TableName'',  '
+'i.name as ''IndexName'',  '
+'i.indid as ''Indexid'',  '
+'CASE WHEN (i.status & 0x800)     = 0 THEN 0 ELSE 1 END AS ''Primary'',   '
+'CASE WHEN (i.status & 0x10)      = 0 THEN 0 ELSE 1 END AS ''Clustered'',   '
+'CASE WHEN (i.status & 0x2)       = 0 THEN 0 ELSE 1 END AS ''Unique'',   '
+'CASE WHEN (i.status & 0x1)       = 0 THEN 0 ELSE 1 END AS ''IgnoreDupKey'',   '
+'CASE WHEN (i.status & 0x4)       = 0 THEN 0 ELSE 1 END AS ''IgnoreDupRow'',   '
+'CASE WHEN (i.status & 0x1000000) = 0 THEN 0 ELSE 1 END AS ''NoRecompute'',   '
+'i.OrigFillFactor AS ''FillFactor'',  '
+'i.rowcnt as ''EstRowCount'',  '
+'i.reserved * cast(8 as bigint) as ''ReservedKB'',    '
+'i.used * cast(8 as bigint) as ''UsedKB'',    '
+'k.keyno as ''KeyNumber'',  '
+'c.name as ''ColumnName'',  '
+'t.name as ''DataType'',   '
+'c.xprec as ''Precision'',  '
+'c.xscale as ''Scale'',   '
+'c.iscomputed as ''IsComputed'',   '
+'c.isnullable as ''IsNullable'',   '
+'c.collation as ''Collation''  '
+'  '
+'from            [' + @db_name + ']..sysobjects   o with(nolock)  '
+'inner join [' + @db_name + ']..sysindexes   i with(nolock) on o.id    =  i.id  '
+'inner join [' + @db_name + ']..sysindexkeys k with(nolock) on i.id    =  k.id    and    i.indid =  k.indid  '
+'inner join [' + @db_name + ']..syscolumns   c with(nolock) on k.id    =  c.id    and    k.colid =  c.colid   '
+'inner join [' + @db_name + ']..systypes     t with(nolock) on c.xtype =  t.xtype   '
+'  '
+'where o.xtype <> ''S''  '  -- Ignore system objects
+'and i.name not like ''_wa_sys_%''   ' -- Ignore statistics
+'  '
+'order by  '
+'o.name,   '
+'k.indid,  '
+'k.keyno  '

execute sp_executesql @sql_string

fetch next from db_cursor into @db_name, @DbID
end 

deallocate db_cursor

select * from #tblHistoryIndex

drop table #tblHistoryIndex