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
Please do share your feedback to improve our quality.