How to get configuration and properties for your SQL server e.g version, type, licence type, collation

Use the SERVERPROPERTY function with a parameter. There are 30 (ish) of them so the follow gets them all or just cut and past the one you need.

Run this in any DB


SET NOCOUNT ON
SELECT 'BuildClrVersion' as PropertyName ,SERVERPROPERTY('BuildClrVersion') as PoprertyValue
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')

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , . Bookmark the permalink.

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