January 12, 2008

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

8 Responses to "How to find out the Sql Server version and service pack"
  1. Anonymous said...
    January 25, 2008 at 1:39 AM

    Excellent i learned new things

  2. Anonymous said...
    January 29, 2008 at 5:52 AM

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

  3. sudha said...
    April 22, 2008 at 1:54 AM

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

  4. Suprotim Agarwal said...
    April 22, 2008 at 7:29 PM

    thanks for your comments Sudha.

  5. Anonymous said...
    August 4, 2008 at 6:19 AM

    can we find liscenece key as well?

  6. Suprotim Agarwal said...
    August 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

  7. Anonymous said...
    April 14, 2009 at 2:29 AM

    it's very usefull blog

  8. DJ said...
    December 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?

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions