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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – 2008 – Inline Variable Assignment
Next Post
SQL SERVER – 2008 – SQL Server Start Time

Related Posts

27 Comments. Leave new

  • devdatta bhosale
    June 29, 2008 8:02 am

    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

    Reply
  • I am getting errors when I tried to run this script on RC0 2008

    Reply
  • i never used this, and i am new at programming.
    do you have an example in delphi for me?
    that works?

    Reply
  • script runs fine on 2K, 2005, CTP6 and RC0

    Reply
  • 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?

    Reply
  • 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

    Reply
    • 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.

      Reply
  • 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 systemnamedb_instance_name to connect to the server?

    Reply
  • Hi,

    i want know data file location using serverproperty.
    If it is possible please let me know.

    Thanks,
    Rajendiran.M

    Reply
  • Hi.
    This is great. I want to know that how can i get Server and client IP Address.

    Thanks,
    Vinod

    Reply
  • 2Asad:

    exec master..sp_dropserver ‘Name_of_server’

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

    After this u must restart the SQL_Server

    Reply
  • how to use the following query in c# using LINQ?

    select serverproperty(‘ServerName’)

    Reply
  • 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?

    Reply
  • 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.

    Reply
    • To check the “ProcessID”, open SQL Server Configuration Manager and click on the SQL Server Services. It shows ProcessID’s of various services running in Sql Server.

      Reply
      • @ Harsha Bhagat.

        Just one more thing. Stop and Start one of the process and you will see that the ProcessID has changed.

  • 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

    Reply
  • how to get list of parameters used inside serverproperty function
    i.e
    1.ServerName
    2. InstanceName
    3. Collation
    etc………….

    Reply
  • Reply
  • This is a great script!

    Reply

Leave a Reply

Menu