SQL SERVER – Introduction to SERVERPROPERTY and example

SERVERPROPERTY is very interesting system function. It returns many of the system values. I use it very frequently to get different server values like Server Collation, Server Name etc.

Run following script to see all the properties of server.
SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

ResultSet:
ColumnName ColumnValue
————————— —————————
BuildClrVersion v2.0.50727
Collation SQL_Latin1_General_CP1_CI_AS
CollationID 872468488
ComparisonStyle 196609
ComputerNamePhysicalNetBIOS SQL
Edition Developer Edition
EditionID -2117995310
EngineEdition 3
InstanceName NULL
IsClustered 0
IsFullTextInstalled 1
IsIntegratedSecurityOnly 0
IsSingleUser 0
LCID 1033
LicenseType DISABLED
MachineName SQL
NumLicenses NULL
ProcessID 3932
ProductVersion 9.00.3054.00
ProductLevel SP2
ResourceLastUpdateDateTime 2007-02-10 00:39:35.480
ResourceVersion 9.00.3042
ServerName SQL
SqlCharSet 1
SqlCharSetName iso_1
SqlSortOrder 52
SqlSortOrderName nocase_iso

(27 row(s) affected)
Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

23 thoughts on “SQL SERVER – Introduction to SERVERPROPERTY and example

  1. I cut this query and try to run in sql2005 but is gives error Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘‘’.
    Please tell me where is problem

  2. This is a great function, but how do you get it to work on a linked server without it returning the value of the server you are running it from?

  3. Hello

    Wonderful script: but I’ve found that SERVERPROPERTY(‘licensetype’) doesn’t work in anything above SQL 2000 without a bit of registry hacking, which is described here

    And that article assumes you know what your licence setup is…

    Have you any brilliant ideas for when you’ve taken over a lot of servers and are unsure as to the licensing status of them?

    Many thanks

    Jane

    • Same thing as I wrote above. If you register the servers in your network in the SMSS under “Registered Servers” (in 2008R2 at least) you can run the query on groups of servers rather than individually. This also works in part on 2005 as well.

  4. Select @@servername
    &
    Select serverproperty(‘servername’)

    Give different values after

    sp_dropserver(‘DB1′)
    sp_addserver(‘TESTSERVER’), ‘LOCAL’

    were executed why? How can i change server name so that i can write just the instance name and not whole systemname\db_instance_name to connect to the server?

  5. 2Asad:

    exec master..sp_dropserver ‘Name_of_server’

    exec master..sp_addserver ‘New_name’, ‘local’

    After this u must restart the SQL_Server

  6. can I get the fully qualified sevre hostname in the serverproperty(“servername”)?

    example, instead of getting server1, I will get server1.abc.com (this hostname of the server)?

    is it possible?

  7. Hi,
    The values what the productversion and processid returns I am not clear with it. Is there any place where we can understand what the values it returns means?

    The processid returns 2248 in SQL server 2000 and 4876 in SQL server 2008.

  8. I have a server called “ACC-WEB-REPORT01″. When I execute “SELECT ‘ServerName’, SERVERPROPERTY(‘ServerName’)” the result is: “ACC-WEB-REPORT0″, so the last character is missing.
    Do you know why? As far as I know the Base data type is nvarchar(128).

    NJ

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s