Script or stored procedure

Question

Script or stored procedure

kaushik.rengarajan

SSC Journeyman

Points: 78
More actions
March 4, 2009 at 12:06 am

#94728

hi i need to search a paarticular string in the all the database of a server( Sql server 2000)
i need into find into tables, sp, views and DTS also.
can u please provide me help!!!!!!!!!!!
URGENT and IMP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mando2 SSChasing Mays Points: 609 More actions · Answer 1

Don't know about DTS. But the easiest option that I can think of for other DB Objects is that you could try and generate SQL script file (been a while since I used EM - but try right clicking on a database in EM and select Generate scripts); you should be able to search in the script file.

If you are looking to do this programatically; you may want to look into INFORMATION_SCHEMA in BOL.

HTH.

vijaysingh SSC Eights! Points: 982 More actions · Answer 2

Hi,

I can help you to find the particular word which are used in sp's

select p.name,m.definition from sys.sql_modules m

Inner join sys.procedures p on p.object_id=m.object_id

where m.definition LIKE '%User%'--Pass the word here

Regards,

Vijay Singh.

vinurajr SSCrazy Points: 2404 More actions · Answer 3

Please follow this link

http://www.sqlservercentral.com/Forums/Topic666390-145-1.aspx%5B/url%5D

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

vijaysingh (3/4/2009)
Hi,
I can help you to find the particular word which are used in sp's
select p.name,m.definition from sys.sql_modules m
Inner join sys.procedures p on p.object_id=m.object_id
where m.definition LIKE '%User%'--Pass the word here
Regards,
Vijay Singh.

sys.sql_modules doesn't exist in SQL Server 2000. You'll need to use syscomments instead, but beware - if the object definition is more than 4000 characters, it will spill over into a new row, and it's possible that the string you're looking for will be split.

John

sanjays-735734 SSC Enthusiast Points: 134 More actions · Answer 5

Hi Buddy

use the built in undocumented procedure

sp_MSforeachdb 'use ?; select ''?'' --rest of your query goes here ',

and this will show you the result from each db

--the ? is dynamically replaced by database name at run time.

Thanks

Sanjay S.

vijaysingh SSC Eights! Points: 982 More actions · Answer 6

Hi John,

Thanks alot for the information provided by you.

As i am new to db i still have to learn more.

Regards,

Vijay Singh.