Find all SPs, FNs, and VWs where particular string occurs

Question

Find all SPs, FNs, and VWs where particular string occurs

VoldemarG

Hall of Fame

Points: 3883
More actions
September 19, 2021 at 11:46 am

#3930945

I have this task to find all SPs, FNs, and VWs where particular string occurs in definition of the object. In 300 databases in a huge server.
How do you suggest that I go about it?

Likes to play Chess

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Redgate's SQL Search?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

VoldemarG

Hall of Fame

Points: 3883

September 19, 2021 at 12:52 pm

#3930953

Any non 3rd party suggestions?

Likes to play Chess

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

Assuming all of the DBs are in source control, you can do a simple string search.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

No source control.... It's a super secret, super ad hoc server...

Likes to play Chess

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 5

Redgate's SQL Search is the best answer - and it is free. If you - for some reason - cannot use that tool then you are going to have to write the code to search the appropriate system tables, and run that code against each database.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

VoldemarG Hall of Fame Points: 3883 More actions · Answer 6

That's what I am doing right now, yes.

And thank you, let me make s strong case for Redgate SQL Search with my mgmt ...

Likes to play Chess

ScottPletcher SSC Guru Points: 100946 More actions · Answer 7

SELECT OBJECT_NAME(object_id) AS object_name, OBJECTPROPERTYEX(object_id, 'BaseType') AS object_type

FROM sys.sql_modules

WHERE definition LIKE N'%<string_you''re_looking_for>%'

ORDER BY 2, 1

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 8

VoldemarG wrote:

I have this task to find all SPs, FNs, and VWs where particular string occurs in definition of the object. In 300 databases in a huge server.
How do you suggest that I go about it?

Scott's answer is similar to what I was going to suggest.

With that, I have to ask, why is it that you need to find a certain string in virtually every programmable object you have?

Also, do you need to check on computed columns as well?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)