Help Index

--David Wootton Enumerate index dependencies and descriptions.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


set nocount on

declare @db varchar(2000)

declare @cmd varchar(8000)

declare @TabName varchar(100)


create table #T (Dbname varchar(500), TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), IndexKeys varchar(200), IndexSize int)


declare cur cursor for

select ltrim(upper('['+name+']'))

from master .. sysdatabases

where status & 512 <> 512

and dbid > 4

order by name

open cur

fetch next from cur into @db

while @@fetch_status = 0 

begin

select @cmd = 'Use'+space(1)+@db



select @cmd = @cmd + '



DECLARE @TabName varchar(100)



DECLARE TCursor CURSOR FAST_FORWARD LOCAL FOR

 

 SELECT ist.table_schema+''.''+ist.table_name 

 FROM sys.sysobjects sys inner join INFORMATION_SCHEMA.TABLES ist on ist.table_name = sys.name

 WHERE sys.xtype = ''U''



OPEN TCursor



FETCH NEXT FROM TCursor INTO @TabName



WHILE @@FETCH_STATUS = 0



 BEGIN



 INSERT INTO #T (IndexName, IndexDescr, IndexKeys)

 EXEC sp_helpindex @TabName

 

 UPDATE #T SET TabName = @TabName WHERE TabName IS NULL

 

 UPDATE #T SET DbName = db_name() where dbname is null

 

 FETCH NEXT FROM TCursor INTO @TabName

 

 END

 

CLOSE TCursor

DEALLOCATE TCursor



DECLARE @ValueCoef int



SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N''E''



UPDATE #T SET IndexSize = 

 ((CAST(sysindexes.used AS bigint) * @ValueCoef )/1024)/1024 

 from

 sys.sysobjects INNER JOIN sys.sysindexes ON sysobjects.id = sysindexes.id



 INNER JOIN #T T ON substring(T.tabname, patindex(''%.%'', T.tabname)+1, len (T.tabname)) collate SQL_Latin1_General_CP1_CI_AS = sysobjects.name 

 AND T.IndexName collate SQL_Latin1_General_CP1_CI_AS = sysindexes.name

'



exec(@cmd)



fetch next from cur into @db



end



close cur 



deallocate cur





SELECT @@servername, dbname, Tabname, indexname, indexdescr, indexkeys, convert(varchar(20), indexSize)+space(1)+'MB' as IndexSize

FROM #T

group by dbname, Tabname, indexname, indexdescr, indexkeys, indexSize

ORDER BY 2, len(indexsize) desc, replace(convert(varchar(20), indexsize), 'MB','') desc



DROP TABLE #T