SQL Server - Lister les propriétés d'une instance SQL Server

Ci-dessous une fonction qui peut se révéler très pratique pour obtenir, en une fois, toutes les propriétés et informations importantes du Serveur et de l’instance SQL Server.
IF EXISTS (
  SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'FC_SQLServer_Infos'
    AND ROUTINE_SCHEMA = 'dbo' 
    AND ROUTINE_TYPE = 'FUNCTION')
   DROP FUNCTION dbo.FC_SQLServer_Infos;
GO

CREATE FUNCTION dbo.FC_SQLServer_Infos()
RETURNS @produitInfos TABLE(Cle sysname, Valeur sysname)
AS
    BEGIN
      DECLARE @Major sysname,
              @Minor sysname,
              @EngineEdition sysname;

        SET @Major = parsename(CAST(serverproperty('Productversion') AS sysname), 4);
        SET @Minor = parsename(CAST(serverproperty('Productversion') AS sysname), 3);
        SET @EngineEdition = CASE CAST(serverproperty('EngineEdition') AS int)
                                WHEN 1 THEN 'Personal'
                                WHEN 2 THEN 'Standard'
                                WHEN 3 THEN 'Enterprise'
                                WHEN 4 THEN 'Express'
                                WHEN 5 THEN 'Azure'
                                ELSE 'Inconnue (' + CAST(serverproperty('EngineEdition') AS VARCHAR) + ')'
                             END;

        INSERT INTO @produitInfos(Cle, Valeur)
          SELECT 'Instance', @@SERVERNAME
        UNION ALL
          SELECT 'Version', CAST(serverproperty('Productversion') AS sysname)
        UNION ALL
          SELECT 'Majeur', parsename(CAST(serverproperty('Productversion') AS sysname), 4)
        UNION ALL
          SELECT 'Mineur', parsename(CAST(serverproperty('Productversion') AS sysname), 3)
        UNION ALL
          SELECT 'Build', parsename(CAST(serverproperty('Productversion') AS sysname), 2)
        UNION ALL
          SELECT 'BuildVersion', parsename(CAST(serverproperty('Productversion') AS sysname), 1)
        UNION ALL
         SELECT 'Produit', 'Microsoft SQL Server ' + CASE @Major
                                                             WHEN 8 THEN '2000'
                                                             WHEN 9 THEN '2005'
                                                             WHEN 10 THEN
                                                                CASE @Minor
                                                                  WHEN 0  THEN '2008'
                                                                  WHEN 50 THEN '2008R2'
                                                                  ELSE @Major + '.' + @Minor
                                                                END
                                                             WHEN 11 THEN '2012'
                                                             ELSE @Major + '.' + @Minor
                                                          END
        UNION ALL          
          SELECT 'Edition', CAST(serverproperty('Edition') AS sysname)
        UNION ALL          
          SELECT 'EngineEdition', CASE CAST(serverproperty('EngineEdition') AS int)
                                WHEN 1 THEN 'Personal'
                                WHEN 2 THEN 'Standard'
                                WHEN 3 THEN 'Enterprise'
                                WHEN 4 THEN 'Express'
                                WHEN 5 THEN 'Azure'
                                ELSE 'Inconnue (' + CAST(serverproperty('EngineEdition') AS VARCHAR) + ')'
                             END
          
        UNION ALL          
        
          SELECT 'ProductLevel', CAST(serverproperty('ProductLevel') AS sysname)                   
        UNION ALL
          SELECT 'IsFullTextInstalled', 
          CASE cast(serverproperty('IsFullTextInstalled') as int)
                            WHEN 1 THEN 'True'
                            WHEN 0 THEN 'False' 
          END 
       UNION ALL
          SELECT 'IsIntegratedSecurityOnly', 
           CASE CAST(serverproperty('IsIntegratedSecurityOnly') as int)
                            WHEN 1 THEN 'True'
                            WHEN 0 THEN 'False' 
           END         
        UNION ALL
          SELECT 'IsSingleUser',
                  CASE CAST(serverproperty('IsSingleUser') AS int)
                    WHEN 1 THEN 'True'
                    WHEN 0 THEN 'False'
                  END
         UNION ALL
          SELECT 'Est un Cluster',
                  CASE CAST(serverproperty('IsClustered') AS int)
                    WHEN 1 THEN 'True'
                    WHEN 0 THEN 'False'
                  END
          UNION ALL
          SELECT 'Groupes de Disponibilité (AlwaysOn)',
                  CASE CAST(serverproperty('IsHadrEnabled') AS int)
                            WHEN 1 then
                                CASE CAST(serverproperty('HardManagerStatus') AS int)
                                    WHEN 0 THEN 'Activé - En attente'
                                    WHEN 1 THEN 'Activé - En cours d''exécution'
                                    WHEN 2 THEN 'Activé - En erreur'
                                END
                            WHEN 0 THEN 'Désactivé'
                            ELSE 'Non disponible'
                  END;
        RETURN;
    END; 
GO
Ci-dessous un exemple d’utilisation :
SELECT * FROM dbo.FC_SQLServer_Infos()
Résultat :
Cle                                 Valeur
Instance                            SRV-278
Version                             10.50.1600.1
Majeur                              10
Mineur                              50
Build                               1600
BuildVersion                        1
Produit                             Microsoft SQL Server 2008R2
Edition                             Enterprise Edition (64-bit)
EngineEdition                       Enterprise
ProductLevel                        RTM
IsFullTextInstalled                 True
IsIntegratedSecurityOnly            False
IsSingleUser                        False
Est un Cluster                      False
Groupes de Disponibilité (AlwaysOn) Non disponible

Aucun commentaire:

Enregistrer un commentaire

Remarque : Seul un membre de ce blog est autorisé à enregistrer un commentaire.