Searching Stored Procedures & User Defined Functions
-
I would like to be able to search through my stored procedures and functions to find specific text.
For example, I need to find which stored procedures call certain functions, or other stored procedures.
Thank you very much.
Bryan Clauss
-
If you don't have Stored Procedures that exceed 4,000 characters, this will work
SELECT
*
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE '%mail%'
That way, you get a list, you can subsequently work on. However, if you do have longer procedures and depending on the number of expected procedures to change, script them out, use a texteditor of your choice and then reapply them to the server.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
There are a few utilities as well that can help here. We had a press release about SQL Digger last week, which does searching.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Frank,
This works awesome for the Stored Procedures. Thanks!
Is there a table for the User defined Functions like "INFORMATION_SCHEMA.ROUTINES"?
---------------------------------
If you don't have Stored Procedures that exceed 4,000 characters, this will work
SELECT
*
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE '%mail%'
That way, you get a list, you can subsequently work on. However, if you do have longer procedures and depending on the number of expected procedures to change, script them out, use a texteditor of your choice and then reapply them to the server.
--
Frank Kalis
SQL Server MVP
-
INFORMATION_SCHEMA.ROUTINES includes SPS and Functions (all 3 types).
-
No, for UDF's you need to query directly the system tables. I'm a bit short now, since I have a meeting right now and am already late. Search the script section here. Should bring up some good stuff.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You can run this script to see for yourself :
if object_id('TestMyTheory') > 0
drop function TestMyTheory
GO
CREATE Function dbo.TestMyTheory()
RETURNS INT
AS
BEGIN
RETURN 1
END
GO
SELECT
Routine_Name, Routine_Type, Routine_Definition
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE '%TestMyTheory()%'
GO
drop function TestMyTheory
GO
-
Yes, seems to work fine for searching udf's!
-
Haha, Remi, thanks for correcting my on this. This shows how much I deal with UDF's.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
There's some pretty good posts here that already work, but I thought I'd throw in my 2 cents... this is what I use and it will find "whatever" in all stored procs, views, UDF's, and whatever else that lives in the Comments system table. Returns the name and type of object "whatever" is found in. It does have the minor danger of not finding something if it straddles the 4k border in code, but, for me, that's usually an acceptable risk because a "whatever" is many times mentioned more than once in the code I've been working with.
--Find name of proc, view, function, etc containing text in DDL
SELECT DISTINCT so.Name, so.Xtype
FROM SYSOBJECTS so,
SYSCOMMENTS sc
WHERE so.ID = sc.ID
AND sc.Text LIKE '%whatever%'
ORDER BY so.xtype,so.Name
--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)
Intro to Tally Tables and Functions -
Deterministic udf aren't so bad to use Frank... but I'd agree that some function uses can be devastating on performance.
-
Not only that...
You can frequently observe the question why one cannot use GETDATE() inside a UDF. And equally frequent you will see someone suggesting to wrap GETDATE() inside a view and call this view inside the UDF. Now, consider this:
USE northwind
GO
CREATE VIEW foolview
AS
SELECT
GETDATE() AS Jetzt
GO
CREATE FUNCTION fool_me()
RETURNS DATETIME
AS
BEGIN
RETURN (
SELECT
Jetzt
FROM
foolview
 
END
GO
CREATE function you_dont_fool_me(@Jetzt datetime)
RETURNS DATETIME
AS
BEGIN
RETURN @Jetzt
END
GO
DECLARE @Jetzt datetime
SET @Jetzt = GETDATE()
--Test 1 viele Zeilen
SELECT DISTINCT
dbo.fool_me()
FROM
[Order Details] AS od
INNER JOIN
Orders AS o
ON
o.OrderId = od.OrderID
--Test2 eine Zeile
SELECT DISTINCT
dbo.you_dont_fool_me(@Jetzt)
FROM
[Order Details] AS od
INNER JOIN
Orders AS o
ON
o.OrderId = od.OrderID
GO
DROP FUNCTION fool_me
DROP FUNCTION you_dont_fool_me
DROP VIEW foolview
------------------------------------------------------
2005-04-12 14:39:47.160
2005-04-12 14:39:47.170
2005-04-12 14:39:47.130
2005-04-12 14:39:47.180
2005-04-12 14:39:47.190
2005-04-12 14:39:47.230
2005-04-12 14:39:47.190
2005-04-12 14:39:47.200
2005-04-12 14:39:47.250
2005-04-12 14:39:47.170
2005-04-12 14:39:47.220
2005-04-12 14:39:47.230
2005-04-12 14:39:47.140
2005-04-12 14:39:47.150
2005-04-12 14:39:47.150
2005-04-12 14:39:47.240
2005-04-12 14:39:47.210
(17 row(s) affected)
------------------------------------------------------
2005-04-12 14:39:47.120
(1 row(s) affected)
Not only does this prove that statement wrong, but it also shows that such scalar UDF's are calculated on a row-by-row basis. As you've mentioned, performance-killers.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes this is prtty much what you had posted a few weeks back... that's what made me realize how much of a killer it can be.
-
A while ago I posted Strored Procedure for this kind of searches.
http://qa.sqlservercentral.com/scripts/contributions/1234.asp
Leah Kats
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply