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)
27 Comments. Leave new
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
I am getting errors when I tried to run this script on RC0 2008
i never used this, and i am new at programming.
do you have an example in delphi for me?
that works?
script runs fine on 2K, 2005, CTP6 and RC0
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?
Did you ever get an answer to – how do I run serverproperty queries through a linked server?
I use a registered server from the SSMS and run the query from there.
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.
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?
Hi,
i want know data file location using serverproperty.
If it is possible please let me know.
Thanks,
Rajendiran.M
Hi.
This is great. I want to know that how can i get Server and client IP Address.
Thanks,
Vinod
2Asad:
exec master..sp_dropserver ‘Name_of_server’
exec master..sp_addserver ‘New_name’, ‘local’
After this u must restart the SQL_Server
how to use the following query in c# using LINQ?
select serverproperty(‘ServerName’)
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?
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.
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.
@ 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
Are you sure about it? Becuase the return type is nvarchar(128). What does following returns?
select replicate(‘a’,30)+’test’
I found this post because I had the same problem. I found something that says this is actually the Netbios name. Those are limited to 15 characters.
Yes! You are right. The NETBIOS limit is the problem. Thanx for your response.
Wow. I learned something new.
how to get list of parameters used inside serverproperty function
i.e
1.ServerName
2. InstanceName
3. Collation
etc………….
List of serverproperty variables
https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017
This is a great script!
dennylou56 – I am glad you liked it.