Bei Entwicklungen in größeren Umgebungen ist es häufig erforderlich, dass neben einer DEV-Umgebung noch eine TEST, UAT (User Acceptance Test) und ev. auch die Produktionsumgebung auf einem Server vorhanden ist. Eventuell ist es aber auch so, dass sie eine generische Entwicklung vorgenommen haben, die in allen Benutzerdatenbanken vorhanden sein sollte.

Mit Hilfe einer simplen Prozedur, die - wegen der allgemeinen Verfügbarkeit - in die Masterdatenbank implementiert wird, kann man relativ leicht Abhilfe schaffen. Der Prozedur übergeben Sie einfach das Objekt nebst Schema sowie den Objekttypen. Weiterhin übergeben Sie als Option, ob auch Systemdatenbanken (master, model, msdb, tempdb) durchsucht werden sollen. Als Ergebnis erhalten Sie eine Relation mit allen Datenbanken, in denen das Objekt gefunden wurde nebst Zeitstempel der Erstellung.

USE master
GO

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = OBJECT_ID('sp_SearchObject', 'P'))
    DROP PROC dbo.sp_SearchObject
    GO

CREATE PROC dbo.sp_SearchObject
    @ObjectName        nvarchar(255),
    @ObjectType        nvarchar(10),
    @InSystemDB        bit
AS
    SET NOCOUNT ON

    -- needed variables
    DECLARE    @database    sysname
    DECLARE    @ObjectId    int
    DECLARE    @parms       nvarchar(200)
    DECLARE    @sqlstmt     nvarchar(1000)

    -- Creation of temporary output object
    CREATE TABLE #ResultList
    (
        [database]    sysname        NOT NULL,
        [objectid]    int            NULL,
        [objectname]  nvarchar(255)  NULL,
        [objecttype]  nvarchar(5)    NULL,
        [crdate]      datetime       NULL,
        [refdate]     datetime       NULL,
    )

    -- definition of cursor for all affected databases
    DECLARE c CURSOR LOCAL FORWARD_ONLY
    FOR
        SELECT  name
        FROM    master.sys.sysdatabases
        WHERE   name NOT IN ('master', 'model', 'msdb', 'tempdb') OR
                (
                    name IN ('master', 'model', 'msdb', 'tempdb') AND
                    @InSystemDB = 1
                )

    OPEN c

    FETCH NEXT FROM c INTO @database
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        SET    @parms   =    N'@ObjectId int OUTPUT'
        SET    @sqlstmt =    N'SET    @ObjectId = OBJECT_ID(''[' + @database + '].' + @ObjectName + ''', ''' + @ObjectType + ''')'
        EXEC   sp_executeSQL @sqlstmt, @parms, @ObjectId = @ObjectId OUTPUT
       
        SET    @parms   =    N'@database sysname, @objectname nvarchar(255), @objecttype nvarchar(5), @objectid int'
        SET    @sqlstmt =    N'INSERT INTO #ResultList
        ([database], [objectid], [objectname], [objecttype], [crdate], [refdate])
        SELECT @database, id, @objectname, @objecttype, crdate, refdate
        FROM    [' + @database + '].sys.sysobjects
        WHERE    id = @objectid'

        EXEC    sp_executeSQL
                @sqlstmt,
                @parms,
                @database   = @database,
                @objectname = @objectname,
                @objecttype = @objecttype,
                @objectId   = @objectId

        FETCH NEXT FROM c INTO @database
    END

    CLOSE c
    DEALLOCATE c

ExitCode:
    SELECT * FROM #ResultList
    DROP TABLE #ResultList
    SET NOCOUNT OFF
GO

Die nachfolgenden Beispiele zeigen die Verwendung der Prozedur:

EXEC sp_SearchObject @ObjectName = 'dbo.DTSStatus', @ObjectType = 'U', @InSystemDB = 0

Das obige Beispiel sucht nach einer Relation dbo.dtsstatus wobei die Systemdatenbanken ausgeschlossen werden während das nachfolgende Beispiel nach einer Systemprozedur sucht und dabei alle Systemdatenbanken berücksichtigt.

EXEC sp_SearchObject @ObjectName = 'dbo.sp_executeSQL', @ObjectType = 'X', @InSystemDB = 1

Weiterführende Informationen:

• sp_executeSQL
• Verwendung von Cursor
• Erstellung von Stored Procedures

64390 Erzhausen, 23.08.2010
Uwe Ricken
db Berater GmbH