Enumerate the Agent in SQL

Enumerate the Agent Agentusemsdbgoifexists (select * fromsysobjectswherenameNsp_sqlagent_enum_errorlogsandtypeP) dropprocdbo. Counter @

Enumerate the Agent in SQL 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 @

Enumerate the Agent in SQL

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 @rc intdeclare @version intdeclare @pos intdeclare @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.0if (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 stringselect @pos = len(@buf) - charindex(char(92), @buf, 0) + 1-- extract the directory only part, part before the last slashselect @dirname = substring(@errorlog_file, 0,  @pos)-- extract the filename, part after the last slashselect @filename = substring(@errorlog_file, @pos + 1, len(@errorlog_file) - @pos)-- extract the filename with extension, part after dot in @filenameselect @filename_no_ext  = substring(@filename, 0, charindex(N'.', @filename, 0))-- since xp_dirtree does not allow any filters, create temp table to store resultscreate table #t([subdirectory] nvarchar(255) not null,[depth] int not null,[file] int not null)-- add all file names to the temp tableinsert 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 #tgo-- sample usage-- exec msdb.dbo.sp_sqlagent_enum_errorlogs-- go