Post reply

find proc among all databases

  • October 25, 2010 at 4:17 am

    #230357

    hello experts i have 300 databases, want to knw a procedure is belongs to which database??

  • October 25, 2010 at 4:34 am

    #1241476

    This should do the trick:

    IF OBJECT_ID('Tempdb..#Procedures') IS NOT NULL

    DROP TABLE #Procedures

    CREATE TABLE #Procedures (name varchar(128))

    DECLARE @ProcedureName varchar(20)

    SET @ProcedureName = 'sp_start_job'

    DECLARE @sql varchar(4000)

    SET @sql = 'USE [?]; INSERT INTO #Procedures SELECT DB_NAME() FROM sys.procedures WHERE name = ''' + @ProcedureName + ''''

    EXEC sp_MsForEachDB @sql

    SELECT * FROM #Procedures

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • October 25, 2010 at 10:46 am

    #1241744

    The suggested code only works if all your dbs are on the same instance. You have to re-run this for every instance you have if the databases are split between servers or instances.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply