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.
Check for the existance of a particular table (a table that stores your product version or some table which holds product-specific static data)
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 ;
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 )
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,