#0264 – SQL Server – How to check if a database belongs to your product?

One question that I often face from teams is:

Given a set of SQL server databases, how do we identify that a given database belong to our product?

There are various mechanisms that can be used, depending upon how the underlying database design is. I have two mechanisms that I use frequently.

  1. Check for the existance of a particular table (a table that stores your product version or some table which holds product-specific static data)

  2. Many database designs (especially those derived from legacy systems) are such that all tables have a column that is used for various generic purposes (to indicate whether a particular record was modified as part of a data clean-up, or a GUID column to facilitate replication, etc)

    • In such database designs, one can check if all tables have the particular column. Pseudo-code to achieve this check efficiently is shown below:
      USE AdventureWorks2008R2 ;
      FROM sys.tables AS st
      LEFT OUTER JOIN sys.columns AS sc ON st.object_id = sc.object_id
      AND sc.name = ‘BusinessEntityId’
      WHERE st.type = ‘U’
      AND st.object_id IS NOT NULL
      AND sc.object_id IS NULL )
      PRINT ‘All tables contain the column – BusinessEntityId.’ ;
      PRINT ‘Some or all tables do not contain the column – BusinessEntityId.’ ;

    • The benefit of the above query is that it queries the meta-data and is therefore faster than a custom iterative mechanism

I trust this post was helpful. I would also like to know what is the method you use for detecting your product database?

Until we meet next time,

Be courteous. Drive responsibly.


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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.