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.