use master
DROP PROCEDURE IF EXISTS spacehd;
GO
CREATE PROCEDURE spacehd AS
/*crio tabela espaco em disco se não existir*/
if object_id('espacodisco') is null
begin
CREATE TABLE espacodisco (
Montagem varchar (10) ,
Volume varchar (10) ,
[Total_GB] Int,
[Disponivel_GB] Int,
[Disponivel_P] Int,
[Uso_P] Int,
[Data] smalldatetime
)
end
/*obtenho os dados de espaco em disco */
insert into espacodisco
SELECT DISTINCT
VS.volume_mount_point [Montagem] ,
VS.logical_volume_name AS [Volume] ,
CAST(CAST(VS.total_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Total (GB)] ,
CAST(CAST(VS.available_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Espaço Disponível (GB)] ,
CAST(( CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço Disponível ( % )] ,
CAST(( 100 - CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço em uso ( % )]
,GETDATE()
FROM
sys.master_files AS MF
CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.file_id) AS VS
WHERE
CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 < 100
/* select * from espacodisco */
return
/* Para executar a obtenção dos dados bastar colocar a linha abaixo dentro do codigo
EXEC master.dbo.spacehd
*/