How to Find if a Hotfix Is Installed in a Farm By Directly Querying the SQL Data Store

Summary 

This article describes how to find out if a Hotfix is installed in a farm by querying directly the SQL datastore.

Procedure:  

• Log on to the SQL server and open SQL Server Management Studio.
• Once authentication to the SQL server is complete, click New Query.
• Change the Database selector to point to the database which stores the farm data store you wish to query. 
• Copy the following SQL code in the newly recreated query window in the right hand panel of the SQL Server Management Studio console.  

Select substring (cast (RDN as nvarchar), patIndex('%--%', cast(RDN as nvarchar))+2, 100) as 'Hotfix', substring (cast (RDN as nvarchar), 1, patIndex('%--%', cast(RDN as nvarchar))-1) as 'Server'
from keytable where contextID = (Select nodeid from keytable where cast(rdn as nvarchar) = 'MFHotFixFolder')
AND SubString(cast (RDN as nvarchar), patIndex('%--%', cast(RDN as nvarchar))+2, 100) = 'PSE400W2K3R01'

If you wish to list all Hotfixes stored in the database, use the following query.

Select distinct substring (cast (RDN as nvarchar), patIndex('%--%', cast(RDN as nvarchar))+2, 100) as [Hotfix] from keytable where contextID = (Select nodeid from keytable where cast(rdn as nvarchar) = 'MFHotFixFolder')