Script To shrinking log file

USE master
GO
CREATE proc sp_tool_createlog
@helpvarchar(1)= null  , 
@tablename varchar(255)= null, 
@logtablename varchar(255) = null, 
@createlogtable tinyint = null, 
@trigtype char(1)= null  
as
-- M.AYDIN 9 Aug 2002
set nocount on
   if isnull(@help ,'#') = '?'
   begin
exec sp_tool_help 'sp_tool_createlog'
return
   end

declare @collist varchar(8000),@collist2 varchar(8000),@icounter smallint,@collistonerow varchar(8000),
@crscolname char(30),@crsxtype tinyint,@crsisnullable int,@crsxprec tinyint,@crsxscale tinyint,
@crslength int, @logtableschema varchar(8000),@create_params varchar(32),@coltypeparam varchar(255),
@lastcolslength int,@crscolnamevarchar varchar(50),@msg varchar(200),
@tab varchar(10),@enter varchar(10),@space varchar(10)

select  @collist = '',@icounter = 1
select  @logtablename = case when @logtablename is null then rtrim(@tablename) + 'log ' else @logtablename end,
@collistonerow ='',@logtableschema = ''
select @tab = char(9),@enter = char(13),@space = char(32)
if @trigtype is not null and @trigtype not in ('U','D')
begin
select @msg = '@trigtype must be ''U'',''D''  or NULL '
raiserror 60000 @msg 
return 60000
end


declare crs insensitive cursor for 
select 
name,xtype,isnullable,xprec,xscale,length,name
from 
syscolumns 
where 
id = object_id(@tablename) and  xtype <> 189 order by colid
for read only
open crs
fetch crs into @crscolname,@crsxtype,@crsisnullable,@crsxprec,@crsxscale,@crslength,@crscolnamevarchar
while @@fetch_status = 0
begin

select @create_params = CREATE_PARAMS from 
master..spt_datatype_info where ss_dtype = @crsxtype
select @coltypeparam  = case 
when @create_params is null then type_name(@crsxtype)
when @create_params = 'precision,scale' then type_name(@crsxtype) + '(' + convert(varchar(10),@crsxprec) + ',' + convert(varchar(10),@crsxscale) + ')'
else type_name(@crsxtype) + '(' + convert(varchar(10),@crslength) + ')' 
end
select @logtableschema = @logtableschema  + @enter + @tab + @crscolname + @tab + @coltypeparam + @tab + case @crsisnullable when 1 then 'NULL' else 'NOT NULL' end + ','
if datalength(@collistonerow) >= 80
begin
select @collistonerow  = ''
select @collistonerow  = @collistonerow   + case @collistonerow when '' then '' else ',' end +  @crscolnamevarchar
select @collist  =  @collist   + @enter + @tab + case @collist when '' then '' else ',' end +  @crscolnamevarchar
end else
begin
select @collistonerow  = @collistonerow   + case @collistonerow when '' then '' else ',' end +  @crscolnamevarchar
select @collist  = @collist   + case @collist when '' then '' else ',' end +  @crscolnamevarchar
end
fetch crs into @crscolname,@crsxtype,@crsisnullable,@crsxprec,@crsxscale,@crslength,@crscolnamevarchar
end
close crs
deallocate crs
select @logtableschema = 'CREATE TABLE ' + rtrim(@logtablename) + @enter+ @space + '(' + @logtableschema
select @logtableschema = @logtableschema + @enter + @tab + cast('loguser'   as char(30))  + @tab + 'varchar(15) null, ' + @enter + @tab + 
   cast('loghostname' as char(30))  + @tab + 'varchar(25) null, ' + @enter + @tab + 
   cast('logappname'  as char(30))  + @tab + 'varchar(25) null, ' + @enter + @tab + 
   cast('logtype'  as char(30))  + @tab + 'char(1) null ,'     + @enter + @tab + 
   cast('logdate'  as char(30))  + @tab + 'datetime null ' + @enter + @space + ')'
select @logtableschema = @logtableschema + @enter + 'GO'
if isnull(@createlogtable,0) = 1 print @logtableschema 
select @icounter = 1
if @trigtype is not null
print 
'create trigger tr' + lower(@trigtype)  + '_' + rtrim(@tablename) + ' on dbo.' + rtrim(@tablename) +  @enter  + 
'for ' + case when @trigtype = 'D' then 'delete ' else 'update' end  + @enter + 'as ' + @enter +
'insert into ' + rtrim(@logtablename) + 
@enter + @space + '( ' +  
@enter + @tab + @collist + ',loguser,loghostname,logappname,logtype,logdate ' + 
@enter + @space + ')' + 
@enter + 'select ' + 
@enter + @tab +  @collist + 
',suser_sname(),host_name(),app_name(),''' + @trigtype + ''',getdate() ' + 
@enter + 'from ' + 
@enter + @tab + 'deleted' 
else
begin
select @trigtype = 'D'
print 
'create trigger tr' + lower(@trigtype)  + '_' + rtrim(@tablename) + ' on dbo.' + rtrim(@tablename) +  @enter  + 
'for ' + case when @trigtype = 'D' then 'delete ' else 'update' end  + @enter + 'as ' + @enter +
'insert into ' + rtrim(@logtablename) + 
@enter + @space + '( ' +  
@enter + @tab + @collist + ',loguser,loghostname,logappname,logtype,logdate ' + 
@enter + @space + ')' + 
@enter + 'select ' + 
@enter + @tab +  @collist + 
',suser_sname(),host_name(),app_name(),''' + @trigtype + ''',getdate() ' + 
@enter + 'from ' + 
@enter + @tab + 'deleted' 
select @trigtype = 'U'
print 'GO'
print 
'create trigger tr' + lower(@trigtype)  + '_' + rtrim(@tablename) + ' on dbo.' + rtrim(@tablename) +  @enter  + 
'for ' + case when @trigtype = 'D' then 'delete ' else 'update' end  + @enter + 'as ' + @enter +
'insert into ' + rtrim(@logtablename) + 
@enter + @space + '( ' +  
@enter + @tab + @collist + ',loguser,loghostname,logappname,logtype,logdate ' + 
@enter + @space + ')' + 
@enter + 'select ' + 
@enter + @tab +  @collist + 
',suser_sname(),host_name(),app_name(),''' + @trigtype + ''',getdate() ' + 
@enter + 'from ' + 
@enter + @tab + 'deleted' 
end
print 'GO'
set nocount off