Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 NOT IN ('master','msdb','tempdb','model','distribution')
- 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
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement