CREATE procedure maint
@dbname nvarchar(max),
@loc nvarchar(max)
as

set nocount on

declare @cmd1 nvarchar(max),@flag1 int
set @cmd1 = 'use '+@dbname+';'

exec @flag1= sp_executesql @cmd1

select @flag1

declare @flag int,@cmd nvarchar(max)

PRINT 'DBCC CHECKDB operation started for database '+@dbname+char(10)

set @cmd = 'dbcc checkdb('+@dbname+')'
exec @flag=dbo.sp_executesql @cmd

if (@flag=0)
PRINT 'DBCC CHECKDB operation finished for database '+@dbname+char(10)
else begin
PRINT 'DBCC CHECKDB operation FAILED for database '+@dbname+char(10)
GOTO FAIL
end
PRINT 'Shrinking of database '+@dbname+' started'+char(10)

set @cmd = 'dbcc shrinkdatabase ('+''''+@dbname+''''+',40)'

exec @flag=dbo.sp_executesql @cmd

if (@flag=0)
PRINT 'DBCC SHRINK DATABASE operation finished for database '+@dbname+char(10)
else
begin PRINT 'DBCC SHRINK DATABASE operation FAILED for database '+@dbname+char(10)
GOTO FAIL
end

PRINT 'Shrinking of database '+@dbname+' Fininshed'+char(10)

declare @command nvarchar(max)
set @command = @loc+@dbname+'.bak'

set @cmd = 'BACKUP DATABASE '+@dbname+' to disk = '+''''+@command+''''+' with init'
exec @flag=dbo.sp_executesql @cmd

if (@flag=0)
PRINT char(10)+'Backup of database '+@dbname+' Fininshed'+char(10)
else begin
PRINT char(10)+'Backup FAILED for database'+@dbname+char(10)
FAIL: PRINT 'Daily maint for database '+@dbname+' FAILED'+char(10)
GOTO end1

end

PRINT 'Daily maint for database '+@dbname+' finished'+char(10)
end1:

set nocount off

--drop proc maint