use master
DECLARE @nome_logico varchar(50)
DECLARE @tipo varchar(50)
DECLARE @banco_nome varchar(50)
DECLARE @model_desc varchar(50)
DECLARE lista CURSOR SCROLL FOR
select
sys.master_files.name as nome_logico
,sys.master_files.type_desc as tipo
,sys.databases.name as banco_nome
,sys.databases.recovery_model_desc
-- ,sys.master_files.database_id as id_banco_master
-- ,sys.databases.database_id
from sys.master_files
inner join sys.databases on sys.databases.database_id = sys.master_files.database_id
where sys.master_files.type_desc='LOG'
and sys.databases.name!='master'
and sys.databases.name!='model'
and sys.databases.name!='msdb'
and sys.databases.name!='ReportServer'
and sys.databases.name!='ReportServerTempDB' order by sys.databases.name
OPEN lista
-- Primeiro registro
FETCH FIRST FROM lista INTO @nome_logico,@tipo,@banco_nome,@model_desc
WHILE @@FETCH_STATUS = 0
BEGIN
/*
print '---------------------------------------------------------------'
print '-- Nome Banco : '+ @banco_nome + ' Tipo : '+ @tipo + ' Nome Log : '+ @nome_logico
print '---------------------------------------------------------------'
*/
if @model_desc = 'FULL'
begin
print 'use '+ @banco_nome
-- print '/* '+@nome_logico+@tipo+@banco_nome+@model_desc
print 'ALTER DATABASE '+@banco_nome+' SET RECOVERY SIMPLE WITH NO_WAIT'
print 'DBCC SHRINKFILE (N'''+@nome_logico+''' , 0, TRUNCATEONLY)'
print '---------------------------------------------------------------'
end
-- Próximo registro
FETCH NEXT FROM lista INTO @nome_logico,@tipo,@banco_nome,@model_desc
END
CLOSE lista
DEALLOCATE lista
/*
select * from master..sysdatabases where master..sysdatabases.name!='master' and master..sysdatabases.name!='model' and master..sysdatabases.name!='msdb' and master..sysdatabases.name!='ReportServer' order by name
select * from master..sysdatabases order by name
SELECT * FROM sys.master_files
SELECT * FROM sys.databases
*/