How can I spot in what database is a stored procedure with name 'myStoredProcedure'?

Question

There are bunch of databases to the SQL server I am connected.

How should I query the sysobjects in order to spot in what database a stored procedure with name 'myStoredProcedure' is located ?

The query should return the database name.

Thanks

Answer 1

I know you are not asking for this, but I'd really download RedGate's Sql Search add-in for SSMS and use that. It allows you to find any object (proc, table, view, column, etc) on any database easily.

And it's free!

Answer 2

I'd give this a try:

CREATE TABLE ##DatabaseList
                (
                DatabaseName varchar(50)
                ) 

 EXECUTE SP_MSForEachDB 'USE [?]; INSERT INTO ##DatabaseList SELECT DB_NAME() FROM [sys].[objects] WHERE name = "MyStoredProcedure" AND type_desc = "SQL_STORED_PROCEDURE"'

 SELECT * FROM ##DatabaseList

 DROP TABLE ##DatabaseList

That's using the undocumented/ unsupported system stored procedure SP_MSForEachDb and writing any hits to a global temp table, then outputting the contents to the Results window before dropping the table. If you just need to know which database (or databases - there may of course be more than one) has an appropriately named SP, this should do it. If you want to use the output elsewhere as a parameter, it may take a little more work.

By the way, I'm only learning this stuff myself over the last few months so if anyone can critique the above and suggest a better way to go at it I'm happy to receive feedback. Equally, I can answer any further questions posted here to the best of my ability.

Cheers

Answer 3

So out of curiosity I decided to try write this myself, especially since ADG mentioned his solution was using an unsupported, undocumented procedure. This could also be expanded to take a 2nd parameter so where it checks the type = P (stored Proc) you could probably change it to look for other things like views / tables etc.

My solution is a bit long but here goes:

  CREATE PROCEDURE spFindProceduresInDatabases
(
    @ProcedureName NVARCHAR(99)
)
AS
BEGIN

    -- Get all the database names and put them into a table
    DECLARE @Db TABLE (DatabaseName Varchar(99))
    INSERT INTO @Db SELECT name FROM Sys.databases 

    -- Declare a table to hold our results
    DECLARE @results TABLE (DatabaseName VARCHAR(99))

    -- Make a Loop

    -- Declare a variable to be incremented
    DECLARE @count INT 
    SET @count = 0 

    -- Declare the end condition
    DECLARE @endCount INT
    SELECT @endCount = COUNT(*) FROM @Db 

    -- Loop through the databases
    WHILE (@count < @endCount ) 
    BEGIN 
        -- Get the database we are going to look into
        DECLARE @dbWeAreChecking VARCHAR(99)
        SELECT TOP 1 @dbWeAreChecking = DatabaseName FROM @Db 
        DELETE FROM @Db WHERE DatabaseName = @dbWeAreChecking 

        -- Create and execute our query
        DECLARE @Query NVARCHAR(3000)
        SET @Query = N'SELECT @outParam = COUNT(*) FROM '+@dbWeAreChecking+'.sys.sysobjects WHERE type = ''P'' and name = @ProcedureName'

        Declare @outParam INT
        print (@Query)
        DECLARE @ParmDefinition NVARCHAR(500)
        DECLARE @IntVariable INT
        SET @ParmDefinition = N'@ProcedureName VARCHAR(99),@outParam INT OUTPUT'

        SET @IntVariable = 35
        EXECUTE sp_executesql
                @Query ,
                @ParmDefinition,
                @ProcedureName,
                @outParam = @outParam OUTPUT

        -- If we have a result insert it into the results table
        If (@outParam > 0)
        BEGIN
            INSERT INTO @results(DatabaseName) VALUES(@dbWeAreChecking)
        END

        -- Increment the counter
       SET @count = (@count + 1) 
    END 

    -- SELECT ALL OF THE THINGS!!!
    SELECT * FROM @results 

END