set quoted_identifier off
DECLARE varchar(40) ,@cmd varchar(2000) , varchar(500), varchar(25), varchar(500)
set = 'CSAToday'
Set = 'getbloombergvalue'
/*note...when seraching for system names, they may have [ ] around name use below to include*/ --Set = '[[]SupplierDataBBDLDvdCash'
--
-- if is not null
-- SELECT name FROM master.dbo.sysdatabases
-- where 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 "%' + + '%"'
if is not null
begin
set @cmd = @cmd + ' or a.command like "%' + + '%"'
end
Print 'Database Name: MSDB-Jobs'
--Print @cmd
--Exec
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
WHILE (@ <> -1)
BEGIN
IF (@ <> -2)
BEGIN
Print 'Database Name:' +
set @cmd = 'use [' + + '];
select "' + + '" as DB_Name, b.name,a.text from syscomments a join
sysobjects b on a.id=b.id
where text like "%' + + '%"'-- and Xtype in (''v'',''p'')'
if is not null
begin
set @cmd = @cmd +
'or text like "%' + + '%"'
end
exec
--PRINT @cmd
END
FETCH NEXT FROM MC INTO
END
CLOSE MC
DEALLOCATE MC
GO