Posts
820
Comments
681
Trackbacks
1
T-SQL: Find text in all dbs and jobs

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

posted on Wednesday, December 06, 2006 10:58 AM
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 2 and 4 and type the answer here: