Posted By

scholli on 02/10/11


Tagged

sql server 2008


Versions (?)

Shrink all development databases


 / Published in: SQL
 

  1. USE [master]
  2. GO
  3.  
  4. DECLARE @dbname SYSNAME
  5. DECLARE @altercmd NVARCHAR(1000)
  6. DECLARE @shrinkcmd NVARCHAR(1000)
  7.  
  8. DECLARE [dbcursor] CURSOR FOR SELECT [name] FROM sysdatabases
  9.  
  10. OPEN [dbcursor]
  11. FETCH NEXT FROM [dbcursor] INTO @dbname
  12.  
  13. WHILE
  14. @@FETCH_STATUS = 0
  15. BEGIN
  16. IF
  17. (SELECT DATABASEPROPERTYEX(@dbname, 'RECOVERY')) != 'SIMPLE'
  18. AND
  19. @dbname != 'tempdb'
  20. BEGIN
  21. SET @altercmd = 'ALTER DATABASE "' + @dbname
  22. + '" SET RECOVERY SIMPLE'
  23. EXEC (@altercmd)
  24.  
  25. SET @shrinkcmd = 'DBCC SHRINKDATABASE ("' + @dbname + '")'
  26. EXEC (@shrinkcmd)
  27.  
  28. PRINT @dbname
  29. END
  30.  
  31. FETCH NEXT FROM [dbcursor] INTO @dbname
  32. END
  33.  
  34. CLOSE [dbcursor]
  35. DEALLOCATE [dbcursor]

Report this snippet  

You need to login to post a comment.