CODEWITHSUNDEEP
databasesql-server-2008
Rauf
Rauf

Reputation: 12842

Search in multiple databases

I missed one stored procedure named Select_Customer. I have a lot of versions of the same data base, I can not really remember where the procedure. How can I search in multiple databases to find an object ?

Upvotes: 0

Views: 358

Answers (1)

satnhak
satnhak

Reputation: 9861

The stored procedure sp_databases lists all of the databases in the server. So executing:

CREATE TABLE #d (
  DATABASE_NAME VARCHAR(255), 
  DATABASE_SIZE INT, REMARKS NVARCHAR(MAX)
)

INSERT INTO #d EXEC sp_databases

Will give you a table containing all database names.

Next the table [DATABASE NAME].sys.procedures contains a list of all the stored procedures in the database.

So you will need to open a cursor that lists all databases in the server and then use dynamic SQL to look for the stored proc by name in each database. Hopefully you can fill in the details :)

OK; I've filled in the details for you

IF OBJECT_ID('[FindStoredProcOnServer]') IS NOT NULL
    DROP PROCEDURE [FindStoredProcOnServer]
GO

CREATE PROCEDURE [FindStoredProcOnServer]
    @storedProcName VARCHAR(255),
    @databaseName   VARCHAR(255) OUTPUT,
    @debug          BIT = 0
AS
BEGIN
    DECLARE @err            BIT
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @params         NVARCHAR(MAX)
    DECLARE @exists         BIT

    CREATE TABLE #output (DATABASE_NAME VARCHAR(255))

    CREATE TABLE #d (
        DATABASE_NAME VARCHAR(255),
        DATABASE_SIZE INT, 
        REMARKS NVARCHAR(MAX)
    )

    INSERT INTO #d EXEC sp_databases

    DECLARE dbCursor CURSOR FOR SELECT DATABASE_NAME FROM #d

    OPEN dbCursor

    SET @params = '@exists BIT OUTPUT'

    FETCH NEXT FROM dbCursor INTO @databaseName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = '
SELECT 
    @exists = COUNT(*) 
FROM 
    [' + @databaseName + '].sys.procedures 
WHERE
    Name = ''' + @storedProcName + ''''

        IF @debug = 1 PRINT @sql

        EXEC sp_executesql 
            @sql = @sql,
            @params = @params,
            @exists = @exists OUTPUT

        SET @err = @@ERROR
        IF ISNULL(@err, 0) != 0 GOTO ERROR_HANDLER

        IF @exists = 1 INSERT INTO #output VALUES (@databaseName)

        FETCH NEXT FROM dbCursor INTO @databaseName
    END

ERROR_HANDLER:
    CLOSE dbCursor
    DEALLOCATE dbCursor

    IF ISNULL(@err, 0) != 0
        RAISERROR('--- Ending [FindStoredProcOnServer]: Failed', 16, 1)
    ELSE
        SELECT * FROM #output

    RETURN ISNULL(@err, 0)
END

Upvotes: 1

Related Questions