查看trace文件信息

if exists(select * from sysobjects where xtype='P' and name='up_dircmd')
drop procedure up_dircmd
go

CREATE procedure dbo.up_dircmd
@pathfile nvarchar(256),
@mark nvarchar(256)='' --默认匹配全部

AS
/**********
--文件名 :
--用途 :查看trace文件信息
--输入参数 :
--返回值解释 :
--创建者 : summer.yang
--创建日期 : 2005-6-18
--修改者 :
--修改日期 :
--修改注解 :(引用请保留此信息)
--备注说明 :也可以用于查看某个路径下的其他文件信息
**********/
set @pathfile=ltrim(rtrim(@pathfile))
-------
declare @return int
set @return=0
-------
create table #tbl(
rowid int not null identity,
cmd_output nvarchar(1000) null ,
path_file nvarchar(256) null default '',
write_time smalldatetime null )

declare @cmd nvarchar(1000)
set @cmd='dir '+@pathfile
insert into #tbl(cmd_output)
exec master.dbo.xp_cmdshell @cmd
set @return=@@error
if @return<>0
begin
raiserror 50000 'The stored procedure executed failed.'
goto finish
end

update #tbl
set cmd_output=' '
where cmd_output is null
update #tbl
set write_time=case when substring(cmd_output,18,1)='p' then
dateadd(hh,12,convert(smalldatetime,substring(cmd_output,1,17),120) )
else convert(smalldatetime,substring(cmd_output,1,17),120) end,
path_file=substring(cmd_output,len(cmd_output)-charindex(' ',reverse(cmd_output),1)+2,charindex(' ',reverse(cmd_output),1))
where isdate(substring(cmd_output,1,17))=1

if right(@pathfile,1)<>''
begin
if exists(select * from #tbl where path_file=right(@pathfile,charindex('',reverse(@pathfile),1)-1))
set @pathfile=substring(@pathfile,1,len(@pathfile)-charindex('',reverse(@pathfile),1)+1)
else
set @pathfile=@pathfile+''
end

update #tbl
set path_file=@pathfile+path_file
where write_time is not null

if @mark <>'' and @mark is not null
begin
select * from #tbl where cmd_output like '%'+@mark+'%'
end
else
begin
select * from #tbl
end

finish:
---------