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 

SELECT   SERVERPROPERTY('productversion') as productversion

        ,SERVERPROPERTY('productlevel') as productlevel

        ,SERVERPROPERTY('edition') as edition

  which returns exactly what you need like

 productversion       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

It returns  something like

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) 

or

Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)   Feb 26 2008 18:15:01   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) 

Bold numbers are actually the verstions of sql server instances. 

Alternately, using SQL Server Enterprise Manager or SQL Server Management studio expand the list of servers, right click the server and select ‘Properties’.

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