SQL Server Performance

Find Anything!

Discussion in 'Michael Berry' started by MichaelB, Jan 9, 2008.

  1. MichaelB Member

    I have shared this code with others in the fourms but I think it is good enough for me to blog. I create a proc that will go through every db on the server or one you sepcify and look through all code object (proc, view, function, etc) for a particular string you give it (@search). This is one of the most powerful procs you can have. The reason is clear. For example, lets say you wrote a proc last month and you dont remember what you called it, if you remember anything that was in it, you can pass that string into the proc and it will find it. It is used by every database developer I show it to. Now it can be yours for free! create table FIND_WORKING (databasename varchar(15),name varchar(70), type varchar(30))
    GO
    use mydatabase
    --*****************************************************************
    -- Proc Name: Find
    --
    -- Purpose: This proc will find any string in all SQL code on this
    -- server. It will not look in the data itself, but only
    -- in code
    --
    -- Called By: anyone
    --
    -- Created By: Michael F. Berry
    --
    -- Created on: 12/19/2006
    --
    -- Modified By: Michael F. Berry
    --
    -- Modified Dte:1/25/2007
    --
    -- Modified Rsn:Make it put out into one main recordset for clarity
    --*****************************************************************CREATE Procedure [dbo].[Find]
    (@search varchar
    (150),@DB varchar
    (200) = 'ALL'
    )as
    set
    nocount on--declare @search varchar(150)
    --set @search = 'temp'declare
    @databasename as varchar(200)truncate
    table dba.dbo.find_workingselect
    'Searching For: '''+ @search + ''''declare
    Curse cursor local fast_forwardfor
    select
    distinctnamefrom master
    .dbo.sysdatabases wherename
    not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW','indataDB_main','IndataDB_MarketValues','IndataDB_PriorMonth','PartnerControl','PartnerData') and(@db
    = 'all'or
    name = @db)open Curse fetch
    next from Curse into @databasenamewhile
    @@fetch_status = 0beginexec ('use ' + @databasename + '
    insert into DBA.dbo.find_working
    select distinct
    ''' + @databasename + ''', cast(o.[name] as varchar(40)) as objectname,
    o.type -- left(c.text,50) as place
    from
    syscomments c
    inner join
    sysobjects o ON
    c.[id] = o.[id]
    where
    c.[text] like ''%' +@search+ '%'' order by cast(o.[name] as varchar(40))
    ') fetch
    next from Curse into @databasenameend
    close
    Cursedeallocate
    Curseselect databasename
    ,name
    ,typefrom
    mydatabase.dbo.find_workingorder
    by databasename,nameset
    nocount offPut this code on a common database (or master) and put a proc in each db to call it so no matter what DB your in you can call it. It will output the database name, code object name and type. It is handy when you want to see what code touches a table that you are considering changing. Very cool code.

Share This Page