Searching in stored procedures.

Question

Post reply

Searching in stored procedures.

ben.brugman

SSChampion

Points: 13369
More actions
October 11, 2012 at 7:07 am

#263745

I want to find all occurences where two specific fields are compared in stored procedures.
The number of stored procedures is large, the size of stored procedures is large.
Table tablenames are not known and I want to find:
<tableA>.veldnaam = <tableB>.veldnaam
Is there an easy like construction for this?
Or any other method?
The stored procedures are large and the number of stored procedures is large as wel.
Thanks in advance
Ben Brugman
On the site:
http://stackoverflow.com/questions/466130/can-you-search-sql-server-2005-stored-procedure-content
I found:
SELECT o.type_desc AS ROUTINE_TYPE
,o.[name] AS ROUTINE_NAME
,m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.object_id = o.object_id
WHERE m.definition LIKE '%search term here%'

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

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

deepkt SSC Eights! Points: 951 More actions · Answer 1

Hi ,

You can also use below queries :

SELECT DISTINCT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE text like '%search key%'

SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like'%search key%'

ben.brugman SSChampion Points: 13369 More actions · Answer 2

deepkt (10/11/2012)
Hi ,
You can also use below queries :
SELECT DISTINCT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE text like '%search key%'
SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like'%search key%'

I did not use Routines, because it only has the first 8000 characters.

I did not use syscomments because it chops up the string in maximum 8000 characters.

But my main question was how to find all:

<tablename1>.veldnaam = <tablename2>.veldnaam

(veldnaam is known, the tablenames are not know)

Thanks,

Ben

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

Attached is a script that I have had around for a long time. I don't even remember where I found it, or who wrote it, so I can't even give proper credit.

One of the variables is @NumberToExtract, which gives you the number of characters to extract before and after the string you are searching on.

Hope it works!

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You should check out RedGate's SQL Search. It is a free plugin for SSMS and it is very solid tool for this type of thing. http://www.red-gate.com/products/sql-development/sql-search/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ben.brugman SSChampion Points: 13369 More actions · Answer 5

Thanks Michael L.John,

I saved that script, it's usefull.

(For my purpose, it only gives the first occurencen of a string and I want the occurence where the field is compared. So I will keep it for future use. Now because the first occurence often does not contain what I am searching for it does not help.).

Thanks Sean lange,

I use the RedGate's SQL Search. But in this case it didn't help me.

Up to now I didn't find anything handy.

Made a copy of everything I wanted to search.

In the copy I did a lot of replaces:

Replaced the searchstring !@#$%^&*,

Replace all letters, numbers and underscore with an 'A'.

Replaced alle multioccurences of A with a Single A.

Then made sure there was one space before and one space after each '=' sign.

Then did a search on

a.!@#$%^&* = a.!@#$%^&*

This does not take in linebreaks or brackets etc., but at least I have something to start on.

It's not elegant, not nice, not fast, but is works (mainly).

Thanks for your time and attention,

Ben Brugman