Check all SQL Services

---Listet alle Services und deren Status auf der aktuellen Instanz

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 * 
FROM   #servicesservicestatus