Sökprocedur i vanliga objekt i Jeeves

Skapad av Kenny Westermark, Ändrad den Fre, 25 mar., 2022 vid 7:24 E.M. efter Kenny Westermark

CREATE OR ALTER PROCEDURE [dbo].[q_SearchObj]
    @SearchString nvarchar(100),
    @SkipSqlObj bit = 0
AS
BEGIN
    SET NOCOUNT ON;
    
  SET @SearchString = REPLACE(@SearchString, '_', '[_]');
    SELECT o.type, o.name, c.text FROM syscomments c JOIN sys.objects o ON o.object_id = c.id WHERE c.text like '%'+@SearchString+'%' AND @SkipSqlObj = 0
    
  UNION ALL
    SELECT 'MCR', [name], code from JeevesSR..mcr WHERE code like '%'+@SearchString+'%'
    
  UNION ALL
    SELECT 'JSMCR', m.JvsSiteMacroName, m.Code from JeevesSR.dbo.JvsSiteMcr m WHERE m.Code like '%'+@SearchString+'%'
    
  UNION ALL
    SELECT 'SQLJOB', j.name + '/' + js.step_name COLLATE Latin1_General_CI_AS, js.command COLLATE Latin1_General_CI_AS FROM [msdb].[dbo].[sysjobsteps] js 
    INNER JOIN [msdb].[dbo].[sysjobs] j ON j.job_id = js.job_id WHERE command LIKE '%'+@SearchString+'%'
END
GO