Search

Showing posts with label Remove Auto Shrink. Show all posts
Showing posts with label Remove Auto Shrink. Show all posts

Friday, April 6, 2012

Remove Auto Shrink from all databases

DECLARE @databasename varchar(100)  
DECLARE @sqlAlterStatement varchar(500)  
  
DECLARE NastyCursorThing CURSOR READ_ONLY FOR  
SELECT name FROM sys.databases  
WHERE database_id >  4 AND DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1  
  
OPEN NastyCursorThing  
FETCH NEXT FROM NastyCursorThing INTO @databasename  
WHILE @@FETCH_STATUS = 0  
    BEGIN  
    SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_SHRINK OFF WITH NO_WAIT' + CHAR(10)  
    print @sqlAlterStatement  
    EXEC(@sqlAlterStatement)  
    FETCH NEXT FROM NastyCursorThing INTO @databasename  
    END  
  
CLOSE NastyCursorThing  
DEALLOCATE NastyCursorThing