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.

  • 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

    http://www.insidesql.de

  • 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)

  • 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

         &nbsp

     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://www.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