Created
February 10, 2022 04:01
-
-
Save dylansalim3/2f5bdcccd6c13c78111e18d10aaa171b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ============================================= | |
-- Author: <Author,,Name> | |
-- Create date: <Create Date,,> | |
-- Description: <Description,,> | |
-- ============================================= | |
CREATE PROCEDURE [dbo].[RebuildIndexCursor] | |
-- Add the parameters for the stored procedure here | |
AS | |
BEGIN | |
DECLARE @Database VARCHAR(255) | |
DECLARE @Table VARCHAR(255) | |
DECLARE @cmd NVARCHAR(500) | |
DECLARE @fillfactor INT | |
SET @fillfactor = 90 | |
DECLARE DatabaseCursor CURSOR FOR | |
SELECT name FROM master.dbo.sysdatabases | |
WHERE name IN ('EPF_DATA','EPF_OSP', 'EPF_REPORT', 'EPF_AUDIT') | |
ORDER BY 1 | |
OPEN DatabaseCursor | |
FETCH NEXT FROM DatabaseCursor INTO @Database | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + | |
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES | |
WHERE table_type = ''BASE TABLE''' | |
-- create table cursor | |
EXEC (@cmd) | |
OPEN TableCursor | |
FETCH NEXT FROM TableCursor INTO @Table | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
PRINT @Table | |
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9) | |
BEGIN | |
-- SQL 2005 or higher command | |
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' | |
EXEC (@cmd) | |
END | |
ELSE | |
BEGIN | |
-- SQL 2000 command | |
DBCC DBREINDEX(@Table,' ',@fillfactor) | |
END | |
FETCH NEXT FROM TableCursor INTO @Table | |
END | |
CLOSE TableCursor | |
DEALLOCATE TableCursor | |
FETCH NEXT FROM DatabaseCursor INTO @Database | |
END | |
CLOSE DatabaseCursor | |
DEALLOCATE DatabaseCursor | |
END | |
go | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment