Posts
1145
Comments
890
Trackbacks
1
December 2006 Blog Posts
.NET Serialization Basics

http://www.15seconds.com/issue/020903.htm

posted @ Thursday, December 21, 2006 12:03 PM | Feedback (0)
T-SQL: Find text in all dbs and jobs

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

posted @ Wednesday, December 06, 2006 10:58 AM | Feedback (0)