Advertisement
brunobola

Untitled

Mar 24th, 2023
107
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.42 KB | None | 0 0
  1. DECLARE @DATABASE VARCHAR(255)  
  2. DECLARE @TABLE VARCHAR(255)  
  3. DECLARE @cmd NVARCHAR(500)  
  4. DECLARE @fillfactor INT
  5.  
  6. SET @fillfactor = 90
  7.  
  8. DECLARE DatabaseCursor CURSOR FOR  
  9. SELECT name FROM master.dbo.sysdatabases  
  10. WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  
  11. ORDER BY 1  
  12.  
  13. OPEN DatabaseCursor  
  14.  
  15. FETCH NEXT FROM DatabaseCursor INTO @DATABASE  
  16. WHILE @@FETCH_STATUS = 0  
  17. BEGIN  
  18.  
  19.    SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  20.  table_name + '']'' as tableName FROM [' + @DATABASE + '].INFORMATION_SCHEMA.TABLES
  21.  WHERE table_type = ''BASE TABLE'''  
  22.  
  23.    -- create table cursor  
  24.    EXEC (@cmd)  
  25.    OPEN TableCursor  
  26.  
  27.    FETCH NEXT FROM TableCursor INTO @TABLE  
  28.    WHILE @@FETCH_STATUS = 0  
  29.    BEGIN  
  30.  
  31.        IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
  32.        BEGIN
  33.            -- SQL 2005 or higher command
  34.            SET @cmd = 'ALTER INDEX ALL ON ' + @TABLE + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
  35.            EXEC (@cmd)
  36.        END
  37.        ELSE
  38.        BEGIN
  39.           -- SQL 2000 command
  40.           DBCC DBREINDEX(@TABLE,' ',@fillfactor)  
  41.        END
  42.  
  43.        FETCH NEXT FROM TableCursor INTO @TABLE  
  44.    END  
  45.  
  46.    CLOSE TableCursor  
  47.    DEALLOCATE TableCursor  
  48.  
  49.    FETCH NEXT FROM DatabaseCursor INTO @DATABASE  
  50. END  
  51. CLOSE DatabaseCursor  
  52. DEALLOCATE DatabaseCursor
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement