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.


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

8 comments:

Anonymous said...

Excellent i learned new things

Anonymous said...

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

sudha said...

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

Suprotim Agarwal said...

thanks for your comments Sudha.

Anonymous said...

can we find liscenece key as well?

Suprotim Agarwal said...

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

Anonymous said...

it's very usefull blog

DJ said...

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