#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 ;
      GO
      IF NOT EXISTS ( SELECT st.*
      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 )
      BEGIN
      PRINT ‘All tables contain the column – BusinessEntityId.’ ;
      END
      ELSE
      BEGIN
      PRINT ‘Some or all tables do not contain the column – BusinessEntityId.’ ;
      END
      GO


    • 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.

Advertisements

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