Monday, 15 August 2011

Loop : Remove Auto Shrink from all databases!

SQL 2000 Version :
DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT Name FROM sysdatabases
WHERE DBID > 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
SQL 2005/2008 Version :
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

1 comment:

Tim B said...

A quicker way is... This will only give you the script for those databases that have AutoShrink on.

select 'alter database ['+name+'] set Auto_Shrink OFF' from master.sys.databases where database_id > 4 and is_auto_shrink_on = 1