Can you search SQL Server 2005 Stored Procedure content?

Question

SQL Server Server 2005. I'm staring at a database that has 500+ stored procedures, and trying to glean the intricacies of how they interact with the data - particularly in regards to how they insert/modify data. I was hoping to find a search" or "find" functionality that would look at the content of the actual procedure. That way, I could do a search for all procedures that do anything at all with some_table_name. The basic find functionality of SQL Management Studio looks in opened files, and Find in Files only appears to find the content if I already have the sproc opened, and even then only in the ...\Local Settings\Temp\~vs1011.sql temporary type files.

As of right now, the only way I know of getting to the underlying procedure is to right click and select "modify" (or Script Stored Procedure As => Create or Alter). Is there a faster/easier way to search/examine all the sprocs?

Answer 1

Mass export to text files then index them with Google Desktop.

Answer 2

There is an Information_Schema.Routines view that you can use.

select * 
FROM   INFORMATION_SCHEMA.ROUTINES 
WHERE  OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0 
       and OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) like '%search term here%' 
       AND ROUTINE_TYPE='PROCEDURE'

Answer 3

select top 10 * from syscomments

You can also find sp_grep which is a popular, though not-included, procedure which does this.

Answer 4

SELECT OBJECT_NAME(id) AS ObjectName, [Text] AS CodeSnippet 
FROM syscomments (nolock) 
WHERE [TEXT] LIKE '%Whatever You Want To Search For%'

Answer 5

Use the INFORMATION_SCHEMA.ROUTINES table.

Within that table, the ROUTINE_DEFINITION field contains the text of your stored procedures.

SELECT
  R.SPECIFIC_NAME
 ,R.ROUTINE_DEFINITION
FROM
  MyDatabase.INFORMATION_SCHEMA.ROUTINES R
WHERE UPPER(R.ROUTINE_DEFINITION) LIKE '%' + UPPER('DELETE') + '%'

Of course, you can parameterize the place where I hard-coded the word "DELETE". Tested in SQL Server 2005.

Answer 6

Yes.

1. you can select * from sys.syscomments

2. if you you have VS for Database Pro. You can new a database project and import schema from the database and do a search in project.

Answer 7

If you want a friendly interface, I can recommend the inexpensive SQL admin toolset from Idera. In addition to other tools, it has a nice SQL Search utility that finds strings in sprocs (or anywhere else) and helps you to navigate them.

Answer 8

select o.name 
from syscomments c
    inner join sysobjects o on c.id = o.id
where text like '%tableName%'

Answer 9

Use MS SQL Server Managment Studio;

Find Table or SP in TreeView, and right-click on it and select "View Dedpendencies"

That way you can see all dependant objects.

Answer 10

Don't use INFORMATION_SCHEMA.ROUTINES. It cuts off at 4000 characters. Get it from sys.sql_modules instead.

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%'

(As written, this will also return Triggers, Views, and Scalar Functions. Exclude those by type if you want)

Answer 11

I use the following stored procedure I came across a while back:

 CREATE PROC dbo.sp_search_code  
(  
@SearchStr  varchar(100),  
@RowsReturned int = NULL OUT  
)  
AS  
/*************************************************************************************************  
  Copyright © 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved.  

Purpose: To search the stored proceudre, UDF, trigger code for a given keyword.  

Written by: Narayana Vyas Kondreddi  
  http://vyaskn.tripod.com  

Tested on:  SQL Server 7.0, SQL Server 2000  

Date created: January-22-2002 21:37 GMT  

Date modified: February-17-2002 19:31 GMT  

Email:   [email protected]  

Examples:  

To search your database code for the keyword 'unauthorized':  
EXEC sp_search_code 'unauthorized'  

To search your database code for the keyword 'FlowerOrders' and also find out the number of hits:  
DECLARE @Hits int  
EXEC sp_search_code 'FlowerOrders', @Hits OUT  
SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result  
*************************************************************************************************/  
BEGIN  
 SET NOCOUNT ON  

 SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',  
  CASE   
    WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1   
    THEN 'Replication stored procedure'  
    WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1   
    THEN 'Extended stored procedure'      
   WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1   
    THEN 'Stored Procedure'   
   WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1   
    THEN 'Trigger'   
   WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1   
    THEN 'Table-valued function'   
   WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1   
    THEN 'Scalar-valued function'  
    WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1   
    THEN 'Inline function'   
  END AS 'Object type',  
  'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'  
 FROM syscomments c  
  INNER JOIN  
  sysobjects o  
  ON c.id = o.id  
 WHERE c.text LIKE '%' + @SearchStr + '%' AND  
  encrypted = 0    AND  
  (  
  OBJECTPROPERTY(c.id, 'IsReplProc') = 1  OR  
  OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR  
  OBJECTPROPERTY(c.id, 'IsProcedure') = 1  OR  
  OBJECTPROPERTY(c.id, 'IsTrigger') = 1  OR  
  OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR  
  OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR  
  OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1   
  )  

 ORDER BY 'Object type', 'Object name'  

 SET @RowsReturned = @@ROWCOUNT  
END

Answer 12

A completely different way from the above answers is to write a simple program that uases the Microsoft.SqlServer.Management.Smo and Microsoft.SqlServer.Management.Common namespaces. Using these you can iterate over all stored procedures and read the text from them. I have written a program this way to compare the store procedures in two different databases (dev and stage or stage and production) and update the ones that are different.

Here is an example:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SqlObjectChecker
{
    internal class ProcedureUpdater
    {
        internal string UpdateProcedure(string spName, string devConnString, string prodConnString, bool updateDev)
        {
            string returnMessage = "";

            ServerConnection devConnection = new ServerConnection();
            devConnection.ConnectionString = devConnString;
            ServerConnection prodConnection = new ServerConnection();
            prodConnection.ConnectionString = prodConnString;

            try
            {
                devConnection.Connect();
                prodConnection.Connect();

                Server devServer = new Server(devConnection);
                Server prodServer = new Server(prodConnection);

                Database devDatabase = devServer.Databases["Dbname"];
                Database prodDatabase = prodServer.Databases["Dbname"];

                StoredProcedure devStoredProcedure = devDatabase.StoredProcedures[spName];
                if (devStoredProcedure != null)
                {
                    StoredProcedure prodStoredProcedure = prodDatabase.StoredProcedures[spName];
                    if (prodStoredProcedure != null)
                    {
                        if (updateDev)
                        {
                            devStoredProcedure.TextHeader = prodStoredProcedure.TextHeader;
                            devStoredProcedure.TextBody = prodStoredProcedure.TextBody;
                            devStoredProcedure.Alter();
                            returnMessage = "Dev updated";
                        }
                        else
                        {
                            prodStoredProcedure.TextHeader = devStoredProcedure.TextHeader;
                            prodStoredProcedure.TextBody = devStoredProcedure.TextBody;
                            prodStoredProcedure.Alter();
                            returnMessage = "Prod updated.";
                        }
                    }
                    else
                    {
                        returnMessage = "Prod Stored Procedure Name Found.";
                    }
                }
                else
                {
                    returnMessage = "Dev Stored Procedure Name Found.";
                }

                devConnection.Disconnect();
                prodConnection.Disconnect();
            }
            catch (Exception exception)
            {
                returnMessage = exception.Message;
            }

            return returnMessage;
        }
    }
}

Answer 13

I wrote a Perl module that allows me to do that and more. With it I can manipulate views/sprocs (and Perl code) using SQL statements.`#!/usr/bin/perl

# use Codebase;

use strict; use warnings;

Codebase::CreateFunctions(change=>\&change);

exit;

sub change { my $string=shift;

my %H=(
        23 => 30,
        25 => 26,
        27 => 30,
        28 => 30,
        29 => 30,
        31 => 24,
        32 => 24
         );
$string =~ s/InstallStatus *(<>|==|>=|<=|>|=|<) *(23|25|27|28|29|31|32)(\W)/"iNstallsTatus $1 $H{$2}$3"/iges;
return $string;
 } # change

END select name(entries.fullname)||extension(entries.fullname) as Name, grep(m/\W(23|25|27|28|29|31|32|InstallStatus|InstallStatusNew|InstallStatusOld)\W/,objects.definition,3) as LISTING from entries inner join objects on entries.OId = objects.OId where (path(entries.fullname) in ('BETA:/') and entries.kind = 'view') and (((objects.definition like m/\W(TRACKING)\W/) and (objects.definition like m/\WInstallStatus(\W|\s)/)) or ((objects.definition like m/\W(TRACKING_LOG)\W/) and (objects.definition like m/\WInstallStatus(New|OLD)(\W|\s)/))) limit 10

select name(entries.fullname)||extension(entries.fullname) as Name, change(objects.definition) as FILE
    from entries
    inner join objects on entries.OId = objects.OId
    where (path(entries.fullname) in ('BETA:/') and entries.kind = 'view')
        and (((objects.definition like m/\W(TRACKING)\W/) and (objects.definition like m/\WInstallStatus(\W|\s)/))
        or ((objects.definition like m/\W(TRACKING_LOG)\W/) and (objects.definition like m/\WInstallStatus(New|OLD)(\W|\s)/)))
        and (change(objects.definition) <> objects.definition)
limit 10

`