To get to know what service pack is installed on a sql server instance you can obtain the current version of SQL Server and then look up to which service pack it correspond or you may use query against SQL Server instances starting from sql server 2005.
|
1 2 3 |
SELECT SERVERPROPERTY('productversion') as productversion
,SERVERPROPERTY('productlevel') as productlevel
,SERVERPROPERTY('edition') as edition |
which returns exactly what you need like
product version productlevel edition
——————– ——————– ——————–
9.00.3068.00 SP2 Developer Edition
For more information read Microsoft Knowledge Base article 321185.
To determine what is the version of SQL Server inctance run query.
|
1 |
select @@version |
Then look up the version number up in one of the tables below.
For SQL Server 2005:
@@version Version and Service Pack 9.00.4028.00 SQL Server 2005 with service pack 3 9.00.3054.00 SQL Server 2005 with service pack 2 GDR2. 9.00.3050 SQL Server 2005 with service pack 2 GDR1. 9.00.3042 SQL Server 2005 with service pack 2. 2005.90.2047 SQL Server 2005 with service pack 1 2005.90.1399 SQL Server 2005 with no service pack applied
For SQL Server 2000:
@@version Version and Service Pack 8.00.2039 SQL Server 2000 with Service Pack 4 8.00.760 SQL Server 2000 with Service Pack 3a 8.00.760 SQL Server 2000 with Service Pack 3. 8.00.532 SQL Server 2000 with Service Pack 2 8.00.384 SQL Server 2000 with Service Pack 1 8.00.194 SQL Server 2000 with no service pack applied
For SQL Server 7:
version Service Pack 7.00.1063 SQL Server 7 with Service Pack 4 7.00.961 SQL Server 7 with Service Pack 3 7.00.842 SQL Server 7 with Service Pack 2 7.00.699 SQL Server 7 with Service Pack 1 7.00.623 SQL Server 7 with no service pack applied
For SQL Server 6.5:
version Service Pack 6.50.479 SQL Server 6.5 with Service Pack 5a (updated) 6.50.416 SQL Server 6.5 with Service Pack 5a. 6.50.415 SQL Server 6.5 with Service Pack 5 6.50.281 SQL Server 6.5 with Service Pack 4 6.50.258 SQL Server 6.5 with Service Pack 3 6.50.240 SQL Server 6.5 with Service Pack 2 6.50.213 SQL Server 6.5 with Service Pack 1 6.50.201 SQL Server 6.5 with no service pack applied
For SQL Server 6:
version Service Pack 6.00.151 SQL Server 6 with Service Pack 3 6.00.139 SQL Server 6 with Service Pack 2 6.00.124 SQL Server 6 with Service Pack 1 6.00.121 SQL Server 6 with no service pack applied