Search Stored Procedures

Sometimes, especially on very old applications that have gone through several rewrites but are still using the original database, I find myself wondering which stored procedures reference a given table, or each other, or whether changing the name of a view or column name will break something somewhere in the database. There are some tools out there to help this kind of thing, such as Red Gate’s Refactor tool, but at a simpler level if you just need to search your stored procedures for a particular string, you can do it using this query that I just saw come across the Sql Server SQL list on SQL Advice:

I’m not sure where Keith found it, but it works great for my needs, and hopefully you’ll find it useful as well.

  • Peter Bromberg

    Here is another nice one: "find string in datbase":

    CREATE procedure [dbo].[sp_Find_String_In_DB]

    @Search varchar(256)

    as

    declare @oid int

    declare @colname varchar(256)

    declare @tablename varchar(256)

    declare @SelectProc varchar(256)

    declare @Count int

    declare table_cur cursor for

    select object_id,name from sys.objects where type = ‘U’ /*and object_id = 326344277*/ order by name

    open table_cur

    fetch next from table_cur into @OID,@tablename

    while @@Fetch_Status = 0 begin

    declare column_cur cursor for

    select name from sys.columns where object_id = @OID order by name –name = ‘IsSharedACLOnly’

    open column_cur

    fetch next from column_cur into @colname

    drop function dbo.spTempST

    while @@Fetch_Status = 0 begin

    begin try

    SET @SelectProc = ‘create function dbo.spTempST() returns int as begin return (select count(*) from [‘ + @tablename + ‘] where [‘ + @colname + ‘] like ”%’ + @Search + ‘%”) end’

    –set @SelectProc = dbo.LookupTable ( @tablename , @colname , @Search )

    –PRINT @SelectProc

    EXEC (@SelectProc)

    exec @Count = spTempST

    IF @Count > 0 begin

    print ‘Table ‘ + @tablename + ‘ Column ‘ + @colname + ‘ Matches ‘ + @Search

    select @tablename,@colname

    set @SelectProc = ‘select * from ‘ + @tablename

    EXEC (@SelectProc)

    end

    end try

    begin catch

    PRINT ERROR_MESSAGE()

    end catch

    begin try

    drop function dbo.spTempST end try

    begin catch end catch

    fetch next from column_cur into @colname

    end

    close column_cur

    deallocate column_cur

    fetch next from table_cur into @OID,@tablename

    end

    close table_cur

    deallocate table_cur

  • Joggee

    There is another Simple way:

    SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE ‘%PRODUC%’)

    The query produced all the object names. where ever it finds expression like ‘PRODUC‘

    If you wanted to find any table name only then

    SELECT * FROM SYSOBJECTS WHERE NAME LIKE ‘%TA%’ and xtype=‘u’

    For stored procedure pass xtype=‘p’ and for views xtype=‘v’

    If you wish to see the complete article :

    http://blog.joggee.com/?p=119

    Joggee

  • Gregg Stark

    I have my own version of this and I wired it to a hotkey so I simply highlight any word in SSMS and hit my hotkey and it shows me everywhere it is used.

    sqladvice.com/…/SQL-Server-Mana

  • greg

    CREATE procedure [dbo].[usp_stored_proc_find_text]

    (

    @searchtext1 nvarchar(100),

    @searchtext2 nvarchar(100) = ”,

    @searchtext3 nvarchar(100) = ”,

    @searchtext4 nvarchar(100) = ”

    )

    AS

    SELECT DISTINCT

    name AS ‘Name’,

    CASE

    WHEN o.xtype = ‘P’ THEN ‘Stored Procedure’

    WHEN o.xtype = ‘TR’ THEN ‘Trigger’

    WHEN o.xtype = ‘U’ THEN ‘Table’

    WHEN o.xtype = ‘D’ THEN ‘Constraint’

    WHEN o.xtype = ‘F’ THEN ‘Foreign Key’

    WHEN o.xtype = ‘FN’ THEN ‘Function’

    WHEN o.xtype = ‘PK’ THEN ‘Primary Key’

    WHEN o.xtype = ‘S’ THEN ‘System Table’

    WHEN o.xtype = ‘UQ’ THEN ‘Index’

    WHEN o.xtype = ‘V’ THEN ‘View’

    ELSE ‘Other’

    END AS ‘Type Description’,

    o.xtype AS ‘Type’

    FROM sysobjects o, syscomments s

    WHERE

    o.id = s.id

    AND

    text LIKE ‘%’+@searchtext1+’%’

    AND

    text LIKE ‘%’+@searchtext2+’%’

    AND

    text LIKE ‘%’+@searchtext3+’%’

    AND

    text LIKE ‘%’+@searchtext4+’%’

    –AND

    — (o.xtype = ‘P’ OR o.xtype = ‘TR’ OR o.xtype = ‘V’)

    ORDER BY

    o.xtype, name

  • irshad

    hello

  • rajesh

    find string in datbase error in

    Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ‘spTempST’. The stored procedure will still be created.