Search

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  

No comments:

Post a Comment