DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE tablecursor CURSOR FOR
SELECT '[' + Object_schema_name([object_id]) + '].['
+ NAME + ']' AS TableName
FROM sys.tables
ORDER BY NAME
OPEN tablecursor
FETCH next FROM tablecursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName
+ ' REBUILD WITH (FILLFACTOR = '
+ CONVERT(VARCHAR(3), @fillfactor) + ')'
PRINT ( @sql )
EXEC (@sql)
FETCH next FROM tablecursor INTO @TableName
END
CLOSE tablecursor
DEALLOCATE tablecursor
go
Thursday, 14 February 2019
SQL Server Reindexing Query (by Table)
The below query to reindex the SQL Server database index by table