Advanced Search

Shrink all development databases

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.