Search Stored Procedures

[robertfah]

For years I've been looking for a way to search all the SP's in a specific DB for a specific string. In most cases, it's usually when I'm cleaning up our database and find a table that I think is no longer in use. I then need to search the SP's to make sure the table isn't in use. I believe I found a way that works for me and I hope others would find this useful as well. Comments/critiques/suggestions are all welcome!

My Setup:

We have a table in our 'Master' DB which holds all our customer DB information (server name, connection sting (encrypted), DBName, etc.) so I do not have to use sp_msForEachDB but that could easily be used instead of the opening select to get all DB's. Once the DB's names are retrieved, it's a simple loop:

	DECLARE @DBName varchar(20)
	DECLARE @sSQL   varchar(500)

	DECLARE LoopCursor CURSOR FOR
		SELECT DBName AS 'DBName'
		FROM [SCMaster].dbo.Configuration c
		WHERE Active = 1

		OPEN LoopCursor

		FETCH NEXT FROM LoopCursor
		INTO @DBName

		WHILE @@FETCH_STATUS = 0
		BEGIN

			SET @sSQL = '
				SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
				FROM [SC_' + @DBName + '].INFORMATION_SCHEMA.ROUTINES 
				WHERE ROUTINE_DEFINITION LIKE ''%TEXT TO SEARCH FOR%''
				AND ROUTINE_TYPE=''PROCEDURE''
				ORDER BY Routine_Name'
			EXEC(@sSQL)	

			FETCH NEXT FROM LoopCursor
			INTO @DBName
		END

		CLOSE LoopCursor
		DEALLOCATE LoopCursor

SELECT 'DONE'

*Note: All our DB's are prefixed with 'SC_', hence the string concatination in the SP. I also tested this on SQL 2000 and SQL 2008.

 

[dhulbert]

Try This

SELECT	*
FROM	sys.procedures SP
		INNER JOIN
		sys.all_sql_modules SM
		ON
		SP.object_id = SM.object_id
WHERE	SM.definition LIKE '%Your String Here%'

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[robertfah]

SELECT *
FROM sys.procedures SP
INNER JOIN
sys.all_sql_modules SM
ON
SP.object_id = SM.object_id
WHERE SM.definition LIKE '%Your String Here%'

This works well on SQL 2008 but not on SQL 2000 as sys.procedures and sys.all_sql_modules do not exist.

 

[gmmastros]

I do have a couple of comments.

1. Why are you filtering on ROUTINE_TYPE = 'PROCEDURE' ? If you remove the where clause, you would also return results from user defined functions. When looking for a particular string, it may be beneficial to also look in functions.

2. There is a potential flaw with your query. The text of stored procedures (and functions) is obviously stored in the database. The problem is, if the code is longer that 4,000 characters, the text is stored in multiple rows of a system table.

To see what I mean, run this:

Select	ROUTINE_NAME, Count(*) 
From	Information_Schema.Routines 
Group By ROUTINE_NAME
Having Count(*) > 1

This query will show you all of the stored procedures (and functions) your database that are longer than 4000 characters.

The problem is that the string you are looking for may be spread over 2 rows in the view, so your query may not return it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

We use this.

http://www.red-gate.com/products/sql-development/sql-search/

Reputable company and free!



----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[robertfah]

@GMM

1.) At the time, we were only concerned with SP's but I like your suggestion; makes more sense.

2.) This is true...not too sure of a way around it in SQL. I can do it in C#, but haven't figured it out in SQL....gives me something to work on, lol.

@SQLScholar
GREAT program....I use it a lot, but unless I have mine setup wrong, it doesn't work with SQL 2000. I get the button in SMS but it's disabled whenever I connect to a 2000 DB. 2008 though works great.

 

[TysonLPrice]

We use this.

http://www.red-gate.com/products/sql-development/sql-search/

Reputable company and free!

I must be doing something wrong. I downloaded it and all I got was a dll. Just looking I think it is just free if you want to use it in the SQL Developer Bundle which is not free.

Maybe I just can't find where it was installed. Anybody else try downloading it?

 

[SQLScholar]

It should be an addin within management studio - as a button on the menu bar.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

as per the first screenshot here:

http://www.red-gate.com/products/sql-development/sql-search/screenshots

I am quite confident it is free for everyone.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[TysonLPrice]

Found it...cool tool. Over the years I've been using some SQL I found on Google. That is much nicer.

Thanks!