---Listet alle Services und deren Status auf der aktuellen Instanz
---Janke 2018 - FITS
 
SET nocount ON 
 
IF ( Object_id ('tempdb..#RegResult') ) IS NOT NULL 
 DROP TABLE #regresult 
 
CREATE TABLE #regresult 
 ( 
 resultvalue NVARCHAR(4) 
 ) 
 
IF ( Object_id ('tempdb..#ServicesServiceStatus') ) IS NOT NULL 
 DROP TABLE #servicesservicestatus 
 
CREATE TABLE #servicesservicestatus 
 ( 
 rowid INT IDENTITY(1, 1), 
 servername NVARCHAR(128), 
 servicename NVARCHAR(128), 
 servicestatus VARCHAR(128), 
 statusdatetime DATETIME DEFAULT (Getdate()), 
 physicalsrvername NVARCHAR(128) 
 ) 
 
IF ( Object_id ('tempdb..#Services') ) IS NOT NULL 
 DROP TABLE #services 
 
CREATE TABLE #services 
 ( 
 rowid INT IDENTITY(1, 1), 
 servicename NVARCHAR(128), 
 defaultinstance NVARCHAR(128), 
 namedinstance NVARCHAR(128) 
 ) 
 
INSERT INTO #services 
VALUES ('MS SQL Server Service', 
 'MSSQLSERVER', 
 'MSSQL'), 
 ('SQL Server Agent Service', 
 'SQLSERVERAGENT', 
 'SQLAgent'), 
 ('Analysis Services', 
 'MSSQLServerOLAPService', 
 'MSOLAP'), 
 ('Full Text Search Service', 
 'MSFTESQL', 
 'MSSQLFDLauncher'), 
 ('Reporting Service', 
 'ReportServer', 
 'ReportServer'), 
 ('SQL Browser Service - Instance Independent', 
 'SQLBrowser', 
 'SQLBrowser'), 
 ('SSIS', 
 'MsDtsServer130', 
 'MsDtsServer110') /* Ändern 'MsDtsServer110' zu 'MsDtsServer100' for SQL 2008 and früher*/ 
DECLARE @ChkInstanceName NVARCHAR(128) 
DECLARE @ChkSrvName NVARCHAR(128) 
DECLARE @REGKEY NVARCHAR(128) 
DECLARE @i INT=1 
DECLARE @Service NVARCHAR(128) 
 
SET @ChkSrvName = Cast(Serverproperty('INSTANCENAME') AS NVARCHAR(128)) 
 
/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/ 
WHILE ( @i <= (SELECT Max(rowid) 
 FROM #services) ) 
 BEGIN 
 IF ( @ChkSrvName IS NULL 
 OR (SELECT Count(*) 
 FROM #services 
 WHERE servicename IN ( 
 'SQL Browser Service - Instance Independent', 
 'SSIS' ) 
 AND rowid = @i) > 0 ) 
 SELECT @Service = defaultinstance 
 FROM #services 
 WHERE rowid = @i 
 ELSE 
 SELECT @Service = namedinstance + '$' 
 + Cast(Serverproperty('INSTANCENAME') AS VARCHAR(128)) 
 FROM #services 
 WHERE rowid = @i 
 
 SET @REGKEY = 'System\CurrentControlSet\Services\' 
 + @Service 
 
 INSERT #regresult 
 (resultvalue) 
 EXEC master.sys.Xp_regread 
 @rootkey='HKEY_LOCAL_MACHINE', 
 @key= @REGKEY 
 
 --PRINT @REGKEY 
 IF (SELECT resultvalue 
 FROM #regresult) = 1 
 BEGIN 
 INSERT INTO #servicesservicestatus 
 (servicestatus) 
 EXEC Xp_servicecontrol 
 N'QUERYSTATE', 
 @Service 
 END 
 ELSE 
 BEGIN 
 INSERT INTO #servicesservicestatus 
 (servicestatus) 
 VALUES ('NOT INSTALLED') 
 END 
 
 UPDATE #servicesservicestatus 
 SET servicename = (SELECT servicename 
 FROM #services 
 WHERE rowid = @i), 
 servername = @@SERVERNAME, 
 physicalsrvername = (SELECT Cast(Serverproperty( 
 'ComputerNamePhysicalNetBIOS')AS 
 VARCHAR(128))) 
 WHERE rowid = @@identity 
 
 TRUNCATE TABLE #regresult 
 
 SET @i=@i + 1; 
 END 
 
/* -------------------------------------------------------------------------------------------------------------*/ 
SELECT 
 
 
 servername 
 ,servicename 
 ,servicestatus 
 
 
 
 
FROM #servicesservicestatus