IF OBJECT_ID('tempdb..#tempVaultStores') IS NOT NULL
BEGIN 
   DROP TABLE #tempVaultStores
END
GO

IF OBJECT_ID('tempdb..#tempArchives') IS NOT NULL
BEGIN
   DROP TABLE #tempArchives
END
GO


CREATE TABLE #tempVaultStores (vaultStoreName varchar(255), DatabaseDSN varchar(255))
GO

CREATE TABLE #tempArchives (archiveName varchar(255), archiveID varchar(255), vaultStoreName varchar(255), itemsAwaitingIndex int, indexServer varchar(255), Failed bit, Rebuilding bit, offline bit)
GO

DECLARE @vaultStoreName varchar(255)
DECLARE @vaultStoreDatabase varchar(255)
DECLARE @SQLQuery varchar(max)

INSERT INTO #tempVaultStores (vaultStoreName, DatabaseDSN)
SELECT VaultStoreName, DatabaseDSN FROM EnterpriseVaultDirectory.dbo.VaultStoreEntry

WHILE (SELECT COUNT(vaultStoreName) FROM #tempVaultStores) > 0
BEGIN
	SELECT @vaultStoreName = vaultStoreName, @vaultStoreDatabase = DatabaseDSN FROM #tempVaultStores
	
	SET @SQLQuery = 'DECLARE @vaultStoreName varchar(255) SET @vaultStoreName = ''' + @vaultStoreName + ''' INSERT INTO #tempArchives (archiveName, archiveID, vaultStoreName, itemsAwaitingIndex, indexServer, Failed, Rebuilding, offline) SELECT A.ArchiveName, R.VaultEntryId, @vaultStoreName AS vaultStoreName, COUNT(JD.IndexCommitted) AS itemsAwaitingIndex, CE.ComputerName, IV.Failed, IV.Rebuilding, IV.Offline FROM EnterpriseVaultDirectory.dbo.Root R, EnterpriseVaultDirectory.dbo.Archive A, EnterpriseVaultDirectory.dbo.IndexVolume IV, EnterpriseVaultDirectory.dbo.IndexRootPathEntry IRP, EnterpriseVaultDirectory.dbo.IndexingServiceEntry ISE, EnterpriseVaultDirectory.dbo.ComputerEntry CE, '+ @vaultStoreDatabase + '.dbo.JournalDelete JD, ' + @vaultStoreDatabase + '.dbo.ArchivePoint AP WHERE JD.ArchivePointIdentity = AP.ArchivePointIdentity AND AP.ArchivePointId = R.VaultEntryId AND R.RootIdentity = A.RootIdentity AND R.RootIdentity = IV.RootIdentity AND IV.IndexRootPathEntryId = IRP.IndexRootPathEntryId AND IRP.IndexServiceEntryId = ISE.ServiceEntryId AND ISE.ComputerEntryId = CE.ComputerEntryId AND JD.IndexCommitted = 0 GROUP BY A.ArchiveName, R.VaultEntryId, CE.ComputerName, IV.Failed, IV.Rebuilding, IV.Offline'
	EXEC (@SQLQuery)
	DELETE FROM #tempVaultStores WHERE vaultStoreName = @vaultStoreName
END

DROP TABLE #tempVaultStores

SELECT archiveName "Archive Name",
       archiveID "Archive ID",
       vaultStoreName "Vault Store",
       itemsAwaitingIndex "Items Awaiting Deletion",
       indexServer "Index Server",
       CASE Failed WHEN 0 THEN 'False' WHEN 1 THEN 'True' END "Index Failed",
       CASE Rebuilding WHEN 0 THEN 'False' WHEN 1 THEN 'True' END "Index Rebuilding",
       CASE Offline WHEN 0 THEN 'False' WHEN 1 THEN 'True' END "Index Offline"
FROM #tempArchives
ORDER BY archiveName

DROP TABLE #tempArchives