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.

Interacting with people on a SQL Server forum is always something that I look forward to. It gives me an insight into the challenges that are being faced out in the field – some of which I may have already encountered & addressed myself in the past, but thought it to be something trivial. A recent question was before me which prompted this post.

A friend of mine was involved in a knowledge transfer of a legacy product from one team to another. Documentation was insufficient and most of the product knowledge was in the brains of the people who had originally developed the system. One of the points covered in the transfer that all databases of that product would have a particular database object (a table or a stored procedure). If that object did not exist, the database was not a valid database for that product. The question therefore was:

“Given a SQL Server instance, is it possible to check for existence of a particular database object across all databases in the instance?”

The simple answer is yes.

However, the answer involves the use of a undocumented (and therefore unsupported) system stored procedure – sp_MSforeachdb.

USE master ;
GO
EXEC sp_MSforeachdb 
       N'USE ?;
         IF EXISTS (SELECT *
                    FROM sys.objects AS so
                    WHERE so.is_ms_shipped = 0
                      AND so.name = ''uspGetBillOfMaterials''
                   )
            SELECT ''?'' AS DatabaseName;' ;
GO

/*RESULTS

DatabaseName
------------------
AdventureWorks2012

*/

Disclaimer: Because of the use of unsupported system stored procedure, it is recommended that the script provided below is limited to use in the development and quality assurance environments only.

Further Reading

• My post #0280 – SQL Server – Script to identify modified objects

• My post #0194-SQL Server-How to get the definition/script for a database object using T-SQL?

Until we meet next time,

Be courteous. Drive responsibly.