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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- ============================ -- -- 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.
