How to find out the Sql Server version and service pack

@@VERSION returns information about the version, processor, build date, copyright info and the operating system of the current SQL Server installation

SELECT @@VERSION as VERSION
returns 'Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6000: ) ' on my machine

SERVERPROPERTY displays information about the server instance

We can use the SERVERPROPERTY with the 'propertyname' to get information about the SQL SERVER instance.

For eg:

To get the product version, use :
SELECT SERVERPROPERTY('productversion') as ProductVersion
returns '9.00.3042.00' on my machine

To get the Service Pack information or the level of version, use :
SELECT SERVERPROPERTY ('productlevel') as Level
returns 'SP2' on my machine

To get the product edition, use :
SELECT SERVERPROPERTY('edition') as EDITION
returns 'Developer Edition' on my machine

Similary you can retrieve the LicenseType, MachineName, Number of client licenses on the current instances, servername and many more property information about the current sql server instance.

Check a list of other properties over here.
at

8 comments:

  1. AnonymousJanuary 25, 2008 at 1:39 AM

    Excellent i learned new things

    ReplyDelete
  2. AnonymousJanuary 29, 2008 at 5:52 AM

    Nice Blog..Usefull..U R Great...

    ReplyDelete
  3. UnknownApril 22, 2008 at 1:54 AM

    select serverproperty('Edition') as SQLEdition, Serverproperty('ProductLevel') as ServicePack,
    ServerProperty('ProductVersion')as Version

    ReplyDelete
  4. Suprotim AgarwalApril 22, 2008 at 7:29 PM

    thanks for your comments Sudha.

    ReplyDelete
  5. AnonymousAugust 4, 2008 at 6:19 AM

    can we find liscenece key as well?

    ReplyDelete
  6. Suprotim AgarwalAugust 4, 2008 at 11:37 PM

    SQL Server 2005 does not track licensing via registry entries. So using the query :

    SELECT ServerProperty('LicenseType')

    'returns DISABLED

    To get a possible resolution to this, check this blog.
    http://blogs.msdn.com/sqlblog/archive/2006/11/10/tracking-license-information-in-sql-2005.aspx

    ReplyDelete
  7. AnonymousApril 14, 2009 at 2:29 AM

    it's very usefull blog

    ReplyDelete
  8. DRDecember 3, 2009 at 1:47 PM

    Any ideas on how to loop through a list of linked servers to extract this information from many, many servers?

    ReplyDelete