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:
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
Post a Comment