set quoted_identifier off
DECLARE @name varchar(40) ,@cmd varchar(2000) ,@TEXT varchar(500),@Database_Name varchar(25),@TEXT2 varchar(500)
set @Database_Name = 'CSAToday'
Set @TEXT = 'getbloombergvalue'
/*note...when seraching for system names, they may have [ ] around name use below to include*/ --Set @TEXT2 = '[[]SupplierDataBBDLDvdCash'
--
-- if @Database_Name is not null
-- SELECT name FROM master.dbo.sysdatabases
-- where name = @Database_Name
-- order by name
-- else
-- SELECT name FROM master.dbo.sysdatabases
-- order by name
-- set @cmd = 'SELECT name FROM master.dbo.sysdatabases
-- order by name '
/*jobs*/
set @cmd ='
select
b.name as Job_Name
,a.command as Command
from
msdb..sysjobsteps a
join msdb..sysjobs b
on a.job_id = b.job_id
where
a.command like "%' + @TEXT + '%"'
if @TEXT2 is not null
begin
set @cmd = @cmd + ' or a.command like "%' + @TEXT2 + '%"'
end
Print 'Database Name: MSDB-Jobs'
--Print @cmd
--Exec (@CMD)
DECLARE MC CURSOR
READ_ONLY
FOR
SELECT name FROM master.dbo.sysdatabases where databasepropertyex(name, 'Status') = 'ONLINE'
order by name
OPEN MC
FETCH NEXT FROM MC INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
Print 'Database Name:' + @name
set @cmd = 'use [' + @name + '];
select "' + @name + '" as DB_Name, b.name,a.text from syscomments a join
sysobjects b on a.id=b.id
where text like "%' + @TEXT + '%"'-- and Xtype in (''v'',''p'')'
if @TEXT2 is not null
begin
set @cmd = @cmd +
'or text like "%' + @TEXT2 + '%"'
end
exec(@cmd)
--PRINT @cmd
END
FETCH NEXT FROM MC INTO @name
END
CLOSE MC
DEALLOCATE MC
GO