Posts Tagged ‘list server property’

SQL Server – SERVERPROPERTY Function

SERVERPROPERTY Function

How does one get list all Server Properties? Well, we can use a very helpful system function SERVERPROPERTY. It will return information about the server instance for a specified property.

Syntax:

SERVERPROPERTY (propertyname)

I have kept the following script in my archives, you may do the same.

— Returns all the property information about the server instance

 

SELECT ‘BuildClrVersion’ AS ‘Server Property’, SERVERPROPERTY(‘BuildClrVersion’) AS ‘Value’

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’)

UNION ALL

SELECT ‘FilestreamShareName’, SERVERPROPERTY(‘FilestreamShareName’)

UNION ALL

SELECT ‘FilestreamConfiguredLevel’, SERVERPROPERTY(‘FilestreamConfiguredLevel’)

UNION ALL

SELECT ‘FilestreamEffectiveLevel’, SERVERPROPERTY(‘FilestreamEffectiveLevel’)

If you want to check what all these different Server Property means, please also see http://msdn.microsoft.com/en-us/library/ms174396.aspx

Good Luck!