Tags

, , ,

SQL Server has little known yet powerful function called SERVERPROPERTY which allows you to extract tremendous amount of information about your SQL Server.

Syntax
SERVERPROPERTY(propertyname)

Below are some example of SERVERPROPERTY.

SELECT 
SERVERPROPERTY('Edition') AS Edition ,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion')AS ProductVersion ,
SERVERPROPERTY('Collation') AS EngineEdition ,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled ,
SERVERPROPERTY('IsSingleUser') AS IsSingleUser ,
SERVERPROPERTY('MachineName') AS MachineName

Result
serverproperty

Check here for full list of parameters.

Why SERVERPROPERTY Why not @@VERSION
Most of the time I have seen DBA’s using @@VERSION  to extract SQL Server properties, but be aware that in some cases using @@VERSION would mislead you, take a below example.
If you run @@VERSION on SQL Server 2005.

SELECT @@VERSION

Result
Microsoft SQL Server 2005 – 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1)

Here Service pack 1 is of operating system not of SQL Server,instead you can make use of SERVERPROPERTY to identify service pack for SQL Server.

SELECT SERVERPROPERTY (‘ProductLevel‘)

Result
SP4

This issues is been fixed in SQL Server 2008 onwards, 2008 onwards @@VERSION returns SP of both SQL and OS.

SELECT @@VERSION

Result
Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1)

New Parameters
In SQL Server 2012 two new parameters are added to SERVERPROPERTY function namely InstanceDefaultDataPath and InstanceDefaultLogPath.
InstanceDefaultDataPath would return you default data directory and InstanceDefaultLogPath would return you log directory.

Hope this helps u!
Praveen

Advertisements