Advertisement
Guest User

Untitled

a guest
Sep 23rd, 2014
211
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.44 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