[SQL Server] Afficher les espaces disques de tous les serveurs SQL Server liés
/*
Auteur : KHARROUBI Zouhaier
Date Création : 08/06/2009
Objectif : controler les espaces disques disponibles sur les serveurs
--Important : le contenu du parametre @SERVEURS doit etre comme le suivant : 'SQLSERVER_1,SQLSERVER_2,SQLSERVER_3,'
*/
CREATE PROCEDURE [dbo].[usp_EspaceDisqueDisponible]
@SERVEURS NVARCHAR(500)=NULL
WITHEXECUTEASOWNER
AS
SET NOCOUNTON
DECLARE @LISTE_SERVEUR VARCHAR(500)
DECLARE @NOM_SRV_LIEN VARCHAR(25)
DECLARE @NOM_SERVEUR VARCHAR(25)
DECLARE @REQUETE NVARCHAR(600)
DECLARE @SEPARATEUR CHAR(1)
DECLARE @POSITION INT
DECLARE @DEBUT INT
DECLARE @TAILLE INT
SET @SEPARATEUR =','
IF EXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#ESPACE_DISQUE_DISPONIBLE]'))
DROP TABLE #ESPACE_DISQUE_DISPONIBLE
IF EXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#TABLE_TEMP]'))
DROP TABLE #TABLE_TEMP
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#NOM_VOLUME]'))
DROP TABLE #NOM_VOLUME
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#OUT_VOLUME]'))
DROP TABLE #OUT_VOLUME
CREATE TABLE #ESPACE_DISQUE_DISPONIBLE(
SERVER_NAME NVARCHAR(35),
LECTEUR CHAR(1),
ESPACE_DISPONIBLE_GO FLOAT, --espace récupérée en GO
NOM_VOLUME VARCHAR (100)
)
CREATE TABLE #TABLE_TEMP(
LECTEUR CHAR(1),
ESPACE_DISPONIBLE_GO FLOAT --espace récupérée en GO
)
CREATE TABLE #NOM_VOLUME(
Caption CHAR(1),
VolumeName VARCHAR (100) NULL
)
CREATE TABLE #OUT_VOLUME(
NUM_LIG INT IDENTITY(1,1),
LIGNE VARCHAR (MAX) NULL
)
IF @SERVEURS ISNULLOR @SERVEURS='ALL'
BEGIN
SET @LISTE_SERVEUR ='SQLSERVER_1'+ @SEPARATEUR +'SQLSERVER_2'+ @SEPARATEUR +'SQLSERVER_3'+ @SEPARATEUR
END
ELSE
SET @LISTE_SERVEUR=@SERVEURS + @SEPARATEUR
SET @POSITION =CHARINDEX(@SEPARATEUR, @LISTE_SERVEUR)
SET @DEBUT = 1
WHILE @POSITION <=LEN(@LISTE_SERVEUR)AND @POSITION>0
BEGIN
SET @TAILLE=@POSITION-@DEBUT
SET @NOM_SRV_LIEN =SubString(@LISTE_SERVEUR, @DEBUT, @TAILLE )
SET @DEBUT = @POSITION + 1
SET @POSITION =CHARINDEX(@SEPARATEUR, @LISTE_SERVEUR,@POSITION+1)
--definir le nom du serveur
SELECT @NOM_SERVEUR=dbo.ServerName(@NOM_SRV_LIEN)
--recuperer les tailles des lecteurs disque
IF @NOM_SERVEUR <>'SQLSERVER_1'
BEGIN
--Récupérer les espaces disques
SET @REQUETE = N'INSERT INTO #TABLE_TEMP '
SET @REQUETE = @REQUETE + N'SELECT * '
SET @REQUETE = @REQUETE + N'FROM OPENROWSET(''SQLNCLI'', ''Server=' + @NOM_SRV_LIEN + N';Trusted_Connection=yes;'','
SET @REQUETE = @REQUETE + N'''SET FMTONLY OFF; EXEC master.dbo.xp_fixeddrives '')'
EXEC (@REQUETE)
--Récupérer les noms des espaces disque
SET @REQUETE = N'INSERT INTO #OUT_VOLUME '
SET @REQUETE = @REQUETE + N'SELECT * '
SET @REQUETE = @REQUETE + N'FROM OPENROWSET(''SQLNCLI'', ''Server=' + @NOM_SRV_LIEN + N';Trusted_Connection=yes;'','
SET @REQUETE = @REQUETE + N'''SET FMTONLY OFF; EXEC sys.xp_cmdshell ''''wmic logicaldisk get caption,volumename'''''')'
EXEC (@REQUETE)
END
ELSE
BEGIN
--Récupérer les espaces disques
INSERT INTO #TABLE_TEMP EXEC master.dbo.xp_fixeddrives
--Récupérer les noms des espaces disque
INSERT INTO #OUT_VOLUME EXEC sys.xp_cmdshell'wmic logicaldisk get caption,volumename'
END
---Récupérer le nom de volume
INSERT INTO #NOM_VOLUME(Caption,VolumeName)
SELECT RTRIM(LTRIM(SUBSTRING(Ligne,0,CHARINDEX(':',Ligne,0))))
,RTRIM(LTRIM(SUBSTRING(Ligne,CHARINDEX(':',Ligne,0)+1,LEN(Ligne)-CHARINDEX(':',Ligne,0)))) FROM #OUT_VOLUME
WHERE CHARINDEX(':',Ligne,0)>0 AND CHARINDEX(':',Ligne,0) IS NOT NULL
INSERT INTO #ESPACE_DISQUE_DISPONIBLE
SELECT @NOM_SERVEUR,T2.Caption , T1.ESPACE_DISPONIBLE_GO, T2.VolumeName FROM #TABLE_TEMP T1
JOIN #NOM_VOLUME T2 ON RTRIM(LTRIM(T2.Caption))=RTRIM(LTRIM(T1.LECTEUR))
TRUNCATE TABLE #TABLE_TEMP
TRUNCATETABLE #NOM_VOLUME
END
--diviser par 1024 pour calculer ESPACE_DISPONIBLE en GO
SELECT SERVER_NAME,LECTEUR,ESPACE_DISPONIBLE_GO/1024 AS ESPACE_DISPONIBLE_GO,NOM_VOLUME
FROM #ESPACE_DISQUE_DISPONIBLE ORDERBY SERVER_NAME,LECTEUR ASC
DROP TABLE #ESPACE_DISQUE_DISPONIBLE
DROP TABLE #TABLE_TEMP
DROP TABLE #NOM_VOLUME
DROP TABLE #OUT_VOLUME