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.