SQL Server Performance

Find anything in a database

Discussion in 'Contribute Your SQL Server Scripts' started by MichaelB, Apr 18, 2007.

  1. MichaelB Member

    This proc will allow you to search your entire SQL server for code. This means you can look for anything referecing a table so you can replace it with an indexed view or whatever. It is very fast and very handy. Run it in text output mode and print it off.. It tells you where the code was found (in what object - sp, view, UDF, etc.) and reminds you of your search criteria so you can review the document later. Enjoy!

    USE [EBS_Common]
    GO
    /****** Object: StoredProcedure [dbo].[Find] Script Date: 04/18/2007 11:26:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    --*****************************************************************
    -- 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:
    --
    -- Modified Dte:
    --
    -- Modified Rsn:
    --*****************************************************************

    CREATE Procedure [dbo].[Find]

    (
    @search varchar(150)
    )

    as

    set nocount on

    declare @databasename as varchar(200)

    declare Curse cursor local fast_forward
    for
    select distinct
    name
    from
    master.dbo.sysdatabases
    where
    name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW','indataDB_main','IndataDB_MarketValues','IndataDB_PriorMonth','PartnerControl','PartnerData')
    open Curse


    fetch next from Curse into @databasename

    while @@fetch_status = 0
    begin

    exec ('use ' + @databasename + '
    select cast(''Searching for "' + @SEARCH + '" in : ' + @databasename + '********'' as varchar(60))



    select distinct
    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 @databasename
    end
    close Curse
    deallocate Curse

    set nocount off


    Michael
    MCDBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7
  2. Luis Martin Moderator

    Did you script the table with EM or SSM?, because using 2000 databases, both GUI script the table different.
  3. MichaelB Member

    Luis,
    I changed it to use a temp table. This will be in SQL Server Magazine this Aug or Sep.USE [DBA]
    GO/****** Object: StoredProcedure [dbo].[Find] Script Date: 04/29/2008 12:48:33 ******/SET
    ANSI_NULLS ONGOSET
    QUOTED_IDENTIFIER ONGO--*****************************************************************
    -- 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 PROC [dbo].[Find]
    (@search varchar
    (150)
    )AS
    SET nocount ON
    DECLARE
    @strSQL as NVARCHAR(800)DECLARE
    @databasename as varchar(200)SELECT 'Searching For: '''+ @search + '''' as Current_Search CREATE
    TABLE #FIND_WORKING (DatabaseName varchar(15),ObjectName varchar(70), ObjectType varchar(30))DECLARE
    Curse CURSOR local fast_forwardFOR
    SELECT nameFROMmaster
    .dbo.sysdatabases WHEREname
    not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW') OPEN Curse FETCH
    next FROM Curse INTO @databasenameWHILE
    @@fetch_status = 0BEGIN
    SET @strSQL= 'use ' + @databasename + '
    insert into #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))
    'EXEC
    dbo.sp_executesql @strSQLfetch
    next from Curse into @databasenameend
    close
    Cursedeallocate
    Curseselect DatabaseName
    ,ObjectName
    ,ObjectTypefrom
    #FIND_WORKINGorder
    by DatabaseName,ObjectNameset
    nocount off

Share This Page