Set @searchKey='log'
Will return all objects that contains the name "log".
-----------------
Set @searchKey='log'
Will return all objects that contains the name "log".
-----------------
Declare @searchKey varchar (100) Declare @SearchObjectNameSql nvarchar (1000) Declare @SearchObjectDefinitionSql nvarchar (1000) DECLARE @SearchObjectDefinition bit DECLARE @DbName sysname /*Do not set DBName to search across all databases*/--SELECT @DbName=DB_NAME() SET @searchKey='SearchObjectName' SET @SearchObjectDefinition = 1 /*Change to 1 to search for SP text,Job step etc*/ SELECT @SearchObjectNameSql = 'USE [?];select ''?'' DB ,name,null,type_desc from sys.objects where name like ''%@searchKey%''' SELECT @SearchObjectNameSql = REPLACE(@SearchObjectNameSql, '@searchKey', @searchKey) SELECT @SearchObjectDefinitionSql = 'USE [?];select DISTINCT ''?'' DB ,so.name, m.[definition], so.type_desc FROM sys.sql_modules m join sys.objects so ON m.object_id=so.object_id WHERE m.[definition] like ''%@searchKey%'' ' SELECT @SearchObjectDefinitionSql = REPLACE(@SearchObjectDefinitionSql, '@searchKey', @searchKey) Declare @List Table ( DBName varchar (500), ObjectName varchar (1000), ObjectDefinition nvarchar (max), Type varchar (100) ) IF @DbName IS NULL BEGIN INSERT @List EXECUTE master.sys.sp_MSforeachdb @SearchObjectNameSql IF @SearchObjectDefinition = 1 BEGIN INSERT @List EXECUTE master.sys.sp_MSforeachdb @SearchObjectDefinitionSql END END ELSE BEGIN select @SearchObjectNameSql=replace(@SearchObjectNameSql,'?',@DbName) select @SearchObjectDefinitionSql=REPLACE(@SearchObjectDefinitionSql,'?',@DbName) INSERT @List execute sp_executesql @SearchObjectNameSql IF @SearchObjectDefinition = 1 BEGIN INSERT @List execute sp_executesql @SearchObjectDefinitionSql END END IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'msdb') BEGIN INSERT @List SELECT 'msdb', j.name, js.command, 'SQL_JOB' FROM msdb.dbo.sysjobs j JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id JOIN master.dbo.sysservers s ON s.srvid = j.originating_server_id WHERE js.command LIKE N'%' + @searchKey + '%' END IF @SearchObjectDefinition = 0 BEGIN SELECT DBName, ObjectName, Type FROM @List ORDER BY DBName, ObjectName END ELSE BEGIN SELECT DBName, ObjectName, ObjectDefinition, CASE WHEN ObjectDefinition IS NULL THEN Type + '_NAME' ELSE Type + '_DEFINITION' END Type FROM @List ORDER BY DBName, ObjectName END