SQL Server : Get information about SQL Server instance like Version, Edition, Service pack , Collation and much more

In order to get information about SQL Server instance like Version, Edition, Service pack, Collation and much more , we have very useful System function SERVERPROPERTY. We can use the below mentioned T-SQL to get all these information:

SELECT 'ComputerName', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'Complete_InstanceName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)