//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: How do I find a stored procedure containing <text>?


How do I find a stored procedure containing <text>?

I see this question at least once a week. Usually, people are trying to find all the stored procedures that reference a specific object. While I think that the best place to do this kind of searching is through your source control tool (you do keep your database objects in source control, don't you?), there are certainly some ways to do this in the database. 
 
Let's say you are searching for 'foobar' in all your stored procedures. You can do this using the INFORMATION_SCHEMA.ROUTINES view, or syscomments: 
 
SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%foobar%' 
    AND ROUTINE_TYPE='PROCEDURE'
 
If you want to present these results in ASP, you can use the following code, which also highlights the searched string in the body (the hW function is based on Article #2344): 
 
<% 
    set conn = CreateObject("ADODB.Connection") 
    conn.Open = "<connection string>" 
 
    ' String we're looking for: 
    str = "foobar" 
 
    sql = "SELECT ROUTINE_NAME, ROUTINE_DEFINITION " & _ 
        "FROM INFORMATION_SCHEMA.ROUTINES " & _ 
        "WHERE ROUTINE_DEFINITION LIKE '%" & str & "%' " & _ 
        " AND ROUTINE_TYPE='PROCEDURE'" 
 
    set rs = conn.execute(sql) 
    if not rs.eof then 
        do while not rs.eof 
            s = hW(str, server.htmlEncode(rs(1))) 
            s = replace(s, vbTab, "&nbsp;&nbsp;&nbsp;&nbsp;") 
            s = replace(s, vbCrLf, "<br>") 
            response.write "<b>" & rs(0) & "</b><p>" & s & "<hr>" 
            rs.movenext 
        loop 
    else 
        response.write "No procedures found." 
    end if 
 
    function hW(strR, tStr) 
        w = len(strR)  
        do while instr(lcase(tStr), lcase(strR)) > 0  
            cPos = instr(lcase(tStr), lcase(strR))  
            nStr = nStr & _  
                left(tStr, cPos - 1) & _  
                "<b>" & mid(tStr, cPos, w) & "</b>"  
            tStr = right(tStr, len(tStr) - cPos - w + 1)  
        loop  
        hW = nStr & tStr  
    end function 
 
    rs.close: set rs = nothing 
    conn.close: set conn = nothing 
%>
 
Another way to perform a search is through the system table syscomments: 
 
SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%foobar%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)
 
Now, why did I use GROUP BY? Well, there is a curious distribution of the procedure text in system tables if the procedure is greater than 8KB. So, the above makes sure that any procedure name is only returned once, even if multiple rows in or syscomments draw a match. But, that begs the question, what happens when the text you are looking for crosses the boundary between rows? Here is a method to create a simple stored procedure that will do this, by placing the search term (in this case, 'foobar') at around character 7997 in the procedure. This will force the procedure to span more than one row in syscomments, and will break the word 'foobar' up across rows. 
 
Run the following query in Query Analyzer, with results to text (CTRL+T): 
 
SET NOCOUNT ON 
SELECT 'SELECT '''+REPLICATE('x', 7936)+'foobar' 
SELECT REPLICATE('x', 500)+''''
 
This will yield two results. Copy them and inject them here: 
 
CREATE PROCEDURE dbo.x 
AS 
BEGIN 
    SET NOCOUNT ON 
    << put both results on this line >> 
END 
GO
 
Now, try and find this stored procedure in INFORMATION_SCHEMA.ROUTINES or syscomments using the same search filter as above. The former will be useless, since only the first 8000 characters are stored here. The latter will be a little more useful, but initially, will return 0 results because the word 'foobar' is broken up across rows, and does not appear in a way that LIKE can easily find it. So, we will have to take a slightly more aggressive approach to make sure we find this procedure. Your need to do this, by the way, will depend partly on your desire for thoroughness, but more so on the ratio of stored procedures you have that are greater than 8KB. In all the systems that I manage, I don't have more than a handful that approach this size, so this isn't something I reach for very often. Maybe for you it will be more useful. 
 
First off, to demonstrate a little better (e.g. by having more than one procedure that exceeds 8KB), let's create a second procedure just like above. Let's call it dbo.y, but this time remove the word 'foobar' from the middle of the SELECT line. 
 
CREATE PROCEDURE dbo.y 
AS 
BEGIN 
    SET NOCOUNT ON 
    << put both results on this line, but replace 'foobar' with something else >> 
END 
GO
 
Basically, what we're going to do next is loop through a cursor, for all procedures that exceed 8KB. We can get this list as follows: 
 
SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id) 
    HAVING COUNT(*) > 1
 
We'll need to create a work table to hold the results as we loop through the procedure, and we'll need to use UPDATETEXT to append each row with the new 8000-or-less chunk of the stored procedure code. 
 
-- create temp table 
CREATE TABLE #temp 

    Proc_id INT, 
    Proc_Name SYSNAME, 
    Definition NTEXT 

 
-- get the names of the procedures that meet our criteria 
INSERT #temp(Proc_id, Proc_Name) 
    SELECT id, OBJECT_NAME(id) 
        FROM syscomments 
        WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 
        GROUP BY id, OBJECT_NAME(id) 
        HAVING COUNT(*) > 1 
 
-- initialize the NTEXT column so there is a pointer 
UPDATE #temp SET Definition = '' 
 
-- declare local variables 
DECLARE  
    @txtPval binary(16),  
    @txtPidx INT,  
    @curName SYSNAME, 
    @curtext NVARCHAR(4000) 
 
-- set up a cursor, we need to be sure this is in the correct order 
-- from syscomments (which orders the 8KB chunks by colid) 
 
DECLARE c CURSOR 
    LOCAL FORWARD_ONLY STATIC READ_ONLY FOR 
    SELECT OBJECT_NAME(id), text 
        FROM syscomments s 
        INNER JOIN #temp t 
        ON s.id = t.Proc_id 
        ORDER BY id, colid 
OPEN c 
FETCH NEXT FROM c INTO @curName, @curtext 
 
-- start the loop 
WHILE (@@FETCH_STATUS = 0) 
BEGIN 
 
    -- get the pointer for the current procedure name / colid 
    SELECT @txtPval = TEXTPTR(Definition) 
        FROM #temp 
        WHERE Proc_Name = @curName 
 
    -- find out where to append the #temp table's value 
    SELECT @txtPidx = DATALENGTH(Definition)/2 
        FROM #temp 
        WHERE Proc_Name = @curName 
 
    -- apply the append of the current 8KB chunk 
    UPDATETEXT #temp.definition @txtPval @txtPidx 0 @curtext 
 
    FETCH NEXT FROM c INTO @curName, @curtext 
END 
 
-- check what was produced 
SELECT Proc_Name, Definition, DATALENGTH(Definition)/2 
    FROM #temp 
 
-- check our filter 
SELECT Proc_Name, Definition 
    FROM #temp 
    WHERE definition LIKE '%foobar%' 
 
-- clean up 
DROP TABLE #temp 
CLOSE c 
DEALLOCATE c
 
Adam Machanic had a slightly different approach to the issue of multiple rows in syscomments, though it doesn't solve the problem where a line exceeds 4000 characters *and* your search phrase teeters on the end of such a line. Anyway, it's an interesting function, check it out
 

SQL Server 2005 
 
Luckily, SQL Server 2005 will get us out of this problem. There are new functions like OBJECT_DEFINITION, which returns the whole text of the procedure. Also, there is a new catalog view, sys.sql_modules, which also holds the entire text, and INFORMATION_SCHEMA.ROUTINES has been updated so that the ROUTINE_DEFINITION column also contains the full text of the procedure. So, any of the following queries will work to perform this search in SQL Server 2005: 
 
SELECT Name 
    FROM sys.procedures 
    WHERE OBJECT_DEFINITION(object_id) LIKE '%foobar%' 
 
SELECT OBJECT_NAME(object_id) 
    FROM sys.sql_modules 
    WHERE Definition LIKE '%foobar%' 
    AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 
 
SELECT ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%foobar%' 
    AND ROUTINE_TYPE = 'PROCEDURE'
 

 
Note that there is no good substitute for documentation around your application. The searching above can provide many irrelevant results if you search for a word that happens to only be included in comments in some procedures, that is part of a larger word that you use, or that should be ignored due to frequency (e.g. SELECT). It can also leave things out if, for example, you are searching for the table name 'Foo_Has_A_Really_Long_Name' and some wise developer has done this: 
 
EXEC('SELECT * FROM Foo_Has' 
    +'_A_Really_Long_Name')
 
Likewise, sp_depends will leave out any procedure like the above, in addition to any procedure that was created before the dependent objects exist. The latter scenario is allowed due to deferred name resolution—the parser allows you to create a procedure that references invalid objects, and doesn't check that they exist until you actually run the stored procedure. 
 
So, long story short, you can't be 100% certain that any kind of searching or in-built query is going to be the silver bullet that tracks down every reference to an object. But you can get pretty close. 
 

Third-party products 
 
Whenever there is a void, someone is going to come up with a solution, right? I was alerted recently of this tool, which indexes all of your metadata to help you search for words... 
 
    Gplex Database 
 
That's the only one I know of that actually indexes the content and metadata of database objects (there are tons of tools that can perform brute force search of files if you have scripted out your objects, but this will roughly equate to the above). If you know of other products, please let us know.

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?

 

 


Created: 7/9/2000 | Last Updated: 9/12/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (44)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...