/ Published in: SQL
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
USE [master] GO DECLARE @dbname SYSNAME DECLARE @altercmd NVARCHAR(1000) DECLARE @shrinkcmd NVARCHAR(1000) DECLARE [dbcursor] CURSOR FOR SELECT [name] FROM sysdatabases OPEN [dbcursor] FETCH NEXT FROM [dbcursor] INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN IF (SELECT DATABASEPROPERTYEX(@dbname, 'RECOVERY')) != 'SIMPLE' AND @dbname != 'tempdb' BEGIN SET @altercmd = 'ALTER DATABASE "' + @dbname + '" SET RECOVERY SIMPLE' EXEC (@altercmd) SET @shrinkcmd = 'DBCC SHRINKDATABASE ("' + @dbname + '")' EXEC (@shrinkcmd) PRINT @dbname END FETCH NEXT FROM [dbcursor] INTO @dbname END CLOSE [dbcursor] DEALLOCATE [dbcursor]