How to get all details about version, SP of SQL Server 2005

I want to find the version, SP appl开发者_开发问答ied (CU if any) applied on SQL server.

I am able to get verison no. using @@VERSION. But I want the whole list of patches/SP applied to it.

Can you please help me out.

Thanks, Sunil

xp_msver tells you a lot.

To back track build (eg 4035 to SQL Server 2005 SP3) to hotfix, check this

Note: Service packs are cumulative. You can't tell if SP1 was applied separately if SP2 is applied. Hotfixes are cumulative at the SP level. So HF Q961930 (HF2) for SQL Server 2005 means SP2 must be installed but you can't assume that Q959195 (HF1) is applied

This snippet of T-SQL gives you a bit more info on the version and features of your SQL Server - I do not know if there's any documented way to find all patches applied to a SQL Server from within it....

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Patch Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'