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

Question

Post reply

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: 246985 More actions · Answer 1

Redgate's SQL Search?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 246985 More actions · Answer 3

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 90304 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: 101120 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 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)