Finding Information About SQL Server Installation Using T-SQL
If you need to gather information about where SQL Server is installed, you can use the following T-SQL query. This script will provide details such as the server instance name, version, authentication mode, and more.
-- Declare a variable to get the SQL Server version
-- declare @version varchar(4)
-- select @version = substring(@@version,22,4)
-- select @@version
-- If SQL version is greater than or equal to 2012, continue
SELECT
SERVERPROPERTY('ServerName') AS [Instance Name],
CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 4)
WHEN '11.0' THEN 'SQL Server 2012'
WHEN '12.0' THEN 'SQL Server 2014'
ELSE 'Newer than SQL Server 2014'
END AS [Version Build],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [Service Pack],
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 0 THEN 'SQL Server and Windows Authentication mode'
WHEN 1 THEN 'Windows Authentication mode'
END AS [Server Authentication],
CASE SERVERPROPERTY('IsClustered')
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END AS [Is Clustered?],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Current Node Name],
SERVERPROPERTY('Collation') AS [SQL Collation],
[cpu_count] AS [CPUs],
[physical_memory_kb] / 1024 AS [RAM (MB)]
FROM
[sys].[dm_os_sys_info];
Example Output:
Version Build Edition Instance Name Service Pack Server Authentication Is Clustered? Current Node Name SQL Collation CPUs RAM (MB)
Newer than SQL Server 2014 Express Edition (64-bit) 23-IT\SQLEXPRESS RTM SQL Server and Windows Authentication mode False 23-IT SQL_Latin1_General_CP1_CI_AS 8 8012
SQL Server Version Greater Than 2005
If your SQL Server version is greater than 2005, you can use the following script to find detailed system properties such as the version, edition, authentication mode, and server information.
SELECT
SERVERPROPERTY('ServerName') AS [Instance Name],
CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 4)
WHEN '9.00' THEN 'SQL Server 2005'
WHEN '10.0' THEN 'SQL Server 2008'
WHEN '10.5' THEN 'SQL Server 2008 R2'
END AS [Version Build],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [Service Pack],
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 0 THEN 'SQL Server and Windows Authentication mode'
WHEN 1 THEN 'Windows Authentication mode'
END AS [Server Authentication],
CASE SERVERPROPERTY('IsClustered')
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END AS [Is Clustered?],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Current Node Name],
SERVERPROPERTY('Collation') AS [SQL Collation],
[cpu_count] AS [CPUs],
[physical_memory_in_bytes] / 1048576 AS [RAM (MB)]
FROM
[sys].[dm_os_sys_info];
For further details on the SQL functions used in these queries, you can refer to the official Microsoft documentation:
