Reindexing SiteAudit Onsite Databases
Reindexing the tables of the SiteAudit database may be beneficial at times, such as before performing a database upgrade in SiteAudit. The script below should work with either Microsoft or Azure SQL.
--reindex tables
IF EXISTS (
SELECT 1
FROM sys.tables
WHERE name = 'devicedata' AND schema_id = SCHEMA_ID('dbo')
)
BEGIN
PRINT 'Reindex tables for ' + DB_NAME() + ' started at ' + FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff')
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER INDEX ALL ON [dbo].[' + @TableName + '] REBUILD WITH (FILLFACTOR = 90)')
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
PRINT 'Reindex tables for ' + DB_NAME() + ' ended at ' + FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff')
END
Notes
Depending on the size of the database, it may take a long period of time to complete.
