时间:2021-07-01 10:21:17 帮助过:38人阅读
枚举SQL中的Agent代理 Agent use msdbgoif exists (select * from sysobjects where name = N'sp_sqlagent_enum_errorlogs' and type ='P') drop proc dbo.sp_sqlagent_enum_errorlogsgocreate proc dbo.sp_sqlagent_enum_errorlogsasset nocount ondeclare @
枚举SQL中的Agent代理 Agentuse msdb go if exists (select * from sysobjects where name = N'sp_sqlagent_enum_errorlogs' and type ='P') drop proc dbo.sp_sqlagent_enum_errorlogs go create proc dbo.sp_sqlagent_enum_errorlogs as set nocount on declare @rc int declare @version int declare @pos int declare @errorlog_file nvarchar(255) declare @filename nvarchar(255) declare @filename_no_ext nvarchar(255) declare @dirname nvarchar(255) declare @buf nvarchar(255) -- SQL Server 7.0 if (charindex(N'7.00', @@version, 0) > 0) begin exec @rc = master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'ErrorLogFile', @errorlog_file OUTPUT, N'no_output' end -- SQL Server 2000 needs to use instance aware Registry read if (charindex(N'8.00', @@version, 0) > 0) begin exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'ErrorLogFile', @errorlog_file OUTPUT, N'no_output' end -- reverse the string to find the last slash '\' select @buf = reverse(@errorlog_file) -- determine posiktion of last slash, now first slash in reversed string select @pos = len(@buf) - charindex(char(92), @buf, 0) + 1 -- extract the directory only part, part before the last slash select @dirname = substring(@errorlog_file, 0, @pos) -- extract the filename, part after the last slash select @filename = substring(@errorlog_file, @pos + 1, len(@errorlog_file) - @pos) -- extract the filename with extension, part after dot in @filename select @filename_no_ext = substring(@filename, 0, charindex(N'.', @filename, 0)) -- since xp_dirtree does not allow any filters, create temp table to store results create table #t ( [subdirectory] nvarchar(255) not null, [depth] int not null, [file] int not null ) -- add all file names to the temp table insert into #t exec master.dbo.xp_dirtree @dirname, 1, 1 -- retrieve only files that match @filename_no_ext% select [name] = subdirectory from #t where subdirectory like @filename_no_ext + '%' order by [name] drop table #t go -- sample usage -- exec msdb.dbo.sp_sqlagent_enum_errorlogs -- go