If you need to do this with a SQL query, you can use xp_cmdshell to run the appropriate wmic command. To clean it up for querying, dump the results into a temporary table, create a second temporary table to clean up the text into a couple columns, and you will be able to view the KB numbers for the patches installed on that machine. Unfortunately, to identify the patches as belonging to SQL Server, you'd need to create and populate another table with a list of existing SQL Server patches, then cross-reference with joins.

Here's some SQL code to get you started...

First, create a temporary table (named #patchtemp in this example) with one column (named "results" in this example). This temp table will allow you to dump the raw results from a windows command ('wmic qfe list brief' for these purposes)...

create table #patchtemp

(

results varchar(1000)

)

Next, run this insert statement to add to this first temporary table, the results of "exec xp_cmdshell 'wmic qfe list brief'". xp_cmdshell procedure will run this command, which is a windows command line WMI query that will return a list of installed patches on the host server.

insert into #patchtemp (results)

exec xp_cmdshell 'wmic qfe list brief';

Now create a second temporary table (named #patch in this example), so you can clean up the results and store only the patch type and KB number...

create table #patch

(

ptype varchar(25),

pnum varchar(10)

)

Then, run an insert statement on #patch temp table to pull in the raw command line results from the first temp table, #patchtemp, clean the text up a bit and break the rows up into relevant strings to represent the patch type, and KB number.

insert into #patch (ptype,pnum)

select left(results,charindex(' ',results)-1),

ltrim(rtrim(substring(results,charindex('KB',results),9) ))

from #patchtemp

where results like '%KB%';

Now you can query the #patch temp table for a list of patches installed on the server.

select * from #patch;

Identifying the product to which a given patch applies will take a bit more work in the way of cross-referencing, in whatever way you come up with. Good news, though, is this will give you a foundation for listing installed patches with a SQL query. Not sure if that's quite what you're looking for, but it has helped me with a particular solution in the past, so might be worth messing with. Hope this helps, if only a little.