How to find sql server version installed and service pack installed.

How to find sql server version installed and service pack installed.

Some time there is need to find what service pack your sql server already have.

1. Go to Sql query analyser

SELECT @@VERSION

It will give u the output something like this:-
=>For Sql server 2000

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright © 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


=>For sql server 2005
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright © 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

But Something is missing here, the service pack info of sql server is missing.

2. Go to Query analyser
SELECT SERVERPROPERTY('productversion')as ProductVersion, SERVERPROPERTY ('productlevel')as ProductLevel, SERVERPROPERTY ('edition') as ServerEdition

=>Output of query for Sql server 2000 is

ProductVersion : ProductLevel : ServerEdition
8.00.760 : SP3 : Enterprise Edition

=>Output of query for Sql server 2005 is

ProductVersion : ProductLevel : ServerEdition
9.00.1399.06 : RTM : Enterprise Edition