declare @databasename nvarchar(100)
--定义游标以及赋值 获取所有Online的Database Name
declare getDataBaseCursor cursor for select name from master.sys.databases where state_desc='online'
--在使用游标之前必须打开它，打开游标
open getDataBaseCursor
--使用fetcth语句取得数据，一条FETCH语句一次可以将一条记录放入指定的变量中,达到Shrink每个DB的效果
fetch next from getDataBaseCursor into @databasename
while @@FETCH_STATUS = 0
begin
declare @RecoveryModel nvarchar(20)
declare @sql nvarchar(100)
declare @logfileid int
select @RecoveryModel= cast(DATABASEPROPERTYEX(name,'RECOVERY') as varchar(20)) from master..sysdatabases where name =@databasename
IF @RecoveryModel<>'SIMPLE'
begin
EXEC ('ALTER DATABASE '+@databasename +' SET RECOVERY SIMPLE')
end
declare @dsql nvarchar(200)
set @dsql=N'select @logfileid = file_id from ' + @databasename+ N'.sys.database_files where type_desc = ''LOG'''
exec sp_executesql @dsql,N'@logfileid int output',@logfileid output
EXEC('use '+@databasename+ ' DBCC SHRINKFILE( '+@logfileid+')')
fetch next from getDataBaseCursor into @databasename
end
--在游标操作的最后请不要忘记关闭游标,以使系统释放游标占用的资源
CLOSE getDataBaseCursor
--删除游标
DEALLOCATE getDataBaseCursor