游标调整数据库属性并收缩数据库
Use master
GO
DECLARE @DBName varchar(200)
DECLARE @ConStrLen int
--定义一个游标
DECLARE SHRINKDBLog CURSOR FOR
--查询系统数据库表中需要的数据库名称
select name from sysdatabases where name like 'DatabaseName%'
OPEN SHRINKDBLog
FETCH NEXT FROM SHRINKDBLog
INTO @DBName
WHILE @@FETCH_STATUS >=0
BEGIN
--调整数据库属性选项栏为简单模式
exec('ALTER DATABASE '+@DBName +' SET RECOVERY SIMPLE')
--调整数据库属性选项栏为自动收缩数据库
exec('ALTER DATABASE '+@DBName +' SET AUTO_SHRINK ON')
--开始收缩数据库.LDF日志文件
exec('DBCC SHRINKDATABASE(['+@DBName+'])')
FETCH NEXT FROM SHRINKDBLog
INTO @DBName
end
CLOSE SHRINKDBLog
DEALLOCATE SHRINKDBLog
GO
Use master
GO
DECLARE @DBName varchar(200)
DECLARE @ConStrLen int
--定义一个游标
DECLARE SHRINKDBLog CURSOR FOR
--查询系统数据库表中需要的数据库名称
select name from sysdatabases where name like 'DatabaseName%'
OPEN SHRINKDBLog
FETCH NEXT FROM SHRINKDBLog
INTO @DBName
WHILE @@FETCH_STATUS >=0
BEGIN
--调整数据库属性选项栏为简单模式
exec('ALTER DATABASE '+@DBName +' SET RECOVERY SIMPLE')
--调整数据库属性选项栏为自动收缩数据库
exec('ALTER DATABASE '+@DBName +' SET AUTO_SHRINK ON')
--开始收缩数据库.LDF日志文件
exec('DBCC SHRINKDATABASE(['+@DBName+'])')
FETCH NEXT FROM SHRINKDBLog
INTO @DBName
end
CLOSE SHRINKDBLog
DEALLOCATE SHRINKDBLog
GO