Truncate logs on all databases with recovery mode of simple

declare @tblSqlCode dbo.ExecSqlStatementsTableType

insert into @tblSqlCode(SqlCode)
SELECT
'use ' + d.name + ';' + CHAR(10)
+ 'Checkpoint;' + CHAR(10)
+ 'DBCC SHRINKFILE([' + mf.name + ']);' + CHAR(10)
FROM
[sys].[databases] d
JOIN [sys].[master_files] mf
ON d.database_id = mf.database_id
WHERE
d.recovery_model = 3 -- simple
AND d.[state] = 0 -- online
AND mf.state = 0 -- online
AND mf.type = 1 -- log
AND d.is_read_only = 0
AND d.name NOT IN ('master', 'tempdb', 'model', 'msdb')


--SELECT * FROM @tblSqlCode

exec dbo.usp_ExecSqlStatements @TableSqlCode=@tblSqlCode, @UseTransactions=0

Execute a series of SQL statements

GO
/****** Object:  StoredProcedure [dbo].[usp_ExecSqlStatements]    Script Date: 10/4/2013 8:03:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE TYPE [dbo].[ExecSqlStatementsTableType] AS TABLE(
[RowId] [int] IDENTITY(1,1) NOT NULL,
[SqlCode] [nvarchar](max) NOT NULL
)
GO


/*


declare @tblSqlCode dbo.ExecSqlStatementsTableType

insert into @tblSqlCode(SqlCode)
values('print ''task 1'''), ('print ''task 2''')

exec dbo.usp_ExecSqlStatements @TableSqlCode=@tblSqlCode


*/
ALTER proc [dbo].[usp_ExecSqlStatements](
@TableSqlCode dbo.ExecSqlStatementsTableType READONLY
,@verbose bit = 0
,@ExecSql bit = 1
,@UseTransactions BIT = 1
)
as


-- ************************************************************************************************
-- ************************************************************************************************
set nocount on
SET XACT_ABORT ON

-- ************************************************************************************************
-- ************************************************************************************************
-- ************************************************************************************************
declare @MaxRows int
declare @row int
declare @sql nvarchar(max)
DECLARE @ErrorMessage NVARCHAR(max);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

-- ************************************************************************************************
-- ************************************************************************************************
-- ************************************************************************************************
select
@row = min(RowId)
,@MaxRows = max(RowId)
from
@TableSqlCode


-- ************************************************************************************************
while @row <= @MaxRows
begin

select
@sql = SqlCode
from
@TableSqlCode
where
RowId = @row

if @sql is not null
begin

if @verbose=1
begin
print @sql
end

if @ExecSql=1
BEGIN

BEGIN TRY
IF @UseTransactions = 1
BEGIN TRAN
exec sp_executesql @sql
IF @UseTransactions = 1
COMMIT tran
END TRY
BEGIN CATCH
   SET @ErrorMessage = 'ErrorMessage=[' + ERROR_MESSAGE() + ']' + CHAR(10)
+ 'ErrorSeverity=[' + CAST(ERROR_SEVERITY() AS NVARCHAR(MAX)) +']' + NCHAR(10)
+ 'ErrorState=[' + CAST(ERROR_STATE() AS NVARCHAR(MAX)) + ']' + NCHAR(10)
+ 'SqlCode=' + NCHAR(10)
+ @sql

IF @@TRANCOUNT > 0 AND @UseTransactions = 1
begin
ROLLBACK TRAN
end

RAISERROR(@ErrorMessage, 9, 1)
END CATCH
       
end
end


set @row = @row +1
end