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.