Skip to main content
Our Tech Ideas

Find SQL Server/Instance details using SQL query

Scenario

Sometimes you need to gather the SQL instance details in a proper format such as Server Name, Instance, Service account name. Today I am sharing a script that will give you the SQL instance details in Single query.

Query

-- ============================ --
-- Owner: SOYELUDDIN BISWAS     --
-- Created Date: 25/10/2019     --
-- Email: st.biswas99@gmail.com --
-- ============================ --
SELECT
SERVERPROPERTY('MachineName') as Server
,servicename as Instance
,SERVERPROPERTY('Edition') AS [Instance Edition]
,CASE 
     WHEN CONVERT(NVARCHAR(15), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL 2005'
     WHEN CONVERT(NVARCHAR(15), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL 2008'
     WHEN CONVERT(NVARCHAR(15), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL 2008 R2'
     WHEN CONVERT(NVARCHAR(15), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL 2012'
     WHEN CONVERT(NVARCHAR(15), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL 2014'
     WHEN CONVERT(NVARCHAR(15), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL 2016'     
     WHEN CONVERT(NVARCHAR(15), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL 2017'
	 WHEN CONVERT(NVARCHAR(15), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL 2019'
	 ELSE 'Please check manually'
  END AS [SQL Product Name]
,SERVERPROPERTY('ProductVersion') AS [Version]
,CASE 
WHEN is_clustered='N' THEN 'Standalone'
WHEN is_clustered='Y' THEN 'Clustered'
END AS [If Clustered/Standalone]
,CASE 
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')=1 THEN 'Windows Authentication'
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')=0 THEN 'Mixed Mode Authentication'
END AS [Authentication Mode]
,status_desc as [Current Status]
,service_account as [Service Account]
FROM sys.dm_server_services

SQL Server Details
SQL Server Details

Please do share your feedback to improve our quality.