A while back, I wrote a post on locating an object in a SQL Server instance (post #351). One of the items I received as part of the feedback was to implement the logic using documented (and therefore supported) features.

As you may know, I had used the sp_MSforeachDB system stored procedure in my earlier post. This is an undocumented procedure and therefore the recommendation is to assume that it is unsupported and not use it in production environments. In today’s post, I present before you a script that achieves the same objective (i.e. locate an object in a SQL Server instance), but using fully documented (and therefore fully supported) features.

Improvements over the script using sp_MSforeachDB:

• Uses fully documented features
• Does a comparison on both – the schema name and the object name
• Search is only limited to user databases by default (system databases can be easily reintroduced by commenting out appropriate filter conditions in the query below)

The entire script is provided below for your reference:

USE master;
GO

SET NOCOUNT ON;

--Declarations
BEGIN
    --User to fill in
    DECLARE @schemaToSearch NVARCHAR(255) 
                            = N'dbo';
    DECLARE @objectToSearch NVARCHAR(255) 
                            = N'uspGetBillOfMaterials';

    --Internal variables
    DECLARE @databaseInformation TABLE 
                (DatabaseId INT NOT NULL,
                 DatabaseName NVARCHAR(255),
                 IsProcessed BIT NOT NULL
                );
    DECLARE @databaseId INT = 0;
    DECLARE @databaseName NVARCHAR(255) = N'';
    DECLARE @sqlStmnt NVARCHAR(1000) = N'';
END

--Fetch all user databases into temporary table variable
--for convenience
BEGIN
    INSERT INTO @databaseInformation (DatabaseId, 
                                      DatabaseName, 
                                      IsProcessed)
    SELECT sdb.database_id AS DatabaseId,
           sdb.name AS DatabaseName,
           0 AS IsProcessed
    FROM sys.databases AS sdb 
    WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) 
                          IN ('master',
                              'msdb',
                              'model',
                              'tempdb',
                              'ReportServer$') 
                          THEN 1 
                     ELSE sdb.is_distributor 
                END) AS BIT) = 0;
END

--Begin processing one database after another
WHILE EXISTS (SELECT dbi.DatabaseId 
              FROM @databaseInformation AS dbi 
              WHERE dbi.IsProcessed = 0
             )
BEGIN
    SELECT TOP 1
           @databaseId = dbi.DatabaseId,
           @databaseName = dbi.DatabaseName
    FROM @databaseInformation AS dbi
    WHERE dbi.IsProcessed = 0;

    --Build the statement to fetch the objectId of an object 
    --and then check for it's existence
    SET @sqlStmnt = N'USE ' + QUOTENAME(@databaseName) + N';
    DECLARE @objectId INT;
    SET @objectId = OBJECT_ID(''' 
                + QUOTENAME(@schemaToSearch) 
                + N'.' 
                + QUOTENAME(@objectToSearch) 
                + N''')
    IF EXISTS (SELECT *
               FROM sys.objects AS so
               WHERE so.is_ms_shipped = 0
               AND so.object_id = @objectId
             )
    BEGIN
        SELECT DB_NAME() AS DatabaseName;
    END;'

    --Execute the query
    --PRINT @sqlStmnt;
    EXEC sp_executesql @sqlStmnt = @sqlStmnt;

    --Mark the database as done
    UPDATE dbi
    SET dbi.IsProcessed = 1
    FROM @databaseInformation AS dbi
    WHERE dbi.DatabaseId = @databaseId
      AND dbi.DatabaseName = @databaseName; 

END
GO

Until we meet next time,
Be courteous. Drive responsibly.