#0351 – SQL Server – Locate an object in a SQL Server instance


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

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

3 thoughts on “#0351 – SQL Server – Locate an object in a SQL Server instance

  1. Alex Yates

    Nice post. I’d never heard of this feature. Thanks for sharing.

    There is also a free tool for this from Redgate which makes searching for objects on databases very easy. Very simple to use:

    SQL Search – http://www.red-gate.com/products/sql-development/sql-search/

    It makes database objects, or any string inside objects (such as dependencies), easy to find.

    Open disclosure. I work for Redgate – but this tool is still free!

    Liked by 1 person

    Reply
  2. Jeff Moden

    It’s a real shame. Up until they came out with 2005, you could press the {f4} key and a nice little tool would come up that would do such searches for you.

    Liked by 1 person

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s