Step 1:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
Step2:
USE [ToolBox]
GO
/****** Object: StoredProcedure [dbo].[GetLocalDiskReportDetails] Script Date: 05-12-2014 18:04:04 ******/
DROP PROCEDURE [dbo].[GetLocalDiskReportDetails]
GO
USE [ToolBox]
GO
/****** Object: StoredProcedure [dbo].[GetLocalDiskReportDetails] Script Date: 05-12-2014 17:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetLocalDiskReportDetails]
--exec GetLocalDiskReportDetails
AS
BEGIN
SET NOCOUNT ON;
DECLARE @p_RecordCounts INT;
DECLARE @p_DriveLetter CHAR(1);
DECLARE @p_SQL_Query NVARCHAR(4000);
DECLARE @p_Drive CHAR(1);
DECLARE @DriveLetterInfo AS TABLE
(
DriveLetter CHAR(1),
MB_free BIGINT
);
-- CREATE TABLE DriveLetterInfoID
DECLARE @DriveLetterInfoID AS TABLE
(
ID INT,
DriveLetter CHAR(1),
MB_free BIGINT
);
--CREATE TABLE DriveDetails
DECLARE @DriveDetails AS TABLE
(
SizeDetails NVARCHAR(1000)
);
--CREATE TABLE DriveDetailsFinal
DECLARE @DriveDetailsFinal AS TABLE
(
Drive CHAR(1),
TotalSize_GB DECIMAL(18,2),
FreeSize_GB DECIMAL(18,2),
UsedSize_GB DECIMAL(18,2)
);
SET @p_SQL_Query = 'EXEC master..xp_fixeddrives';
INSERT INTO @DriveLetterInfo
EXEC (@p_SQL_Query);
--1
print(@p_SQL_Query);
INSERT INTO @DriveLetterInfoID
SELECT ROW_NUMBER() OVER (ORDER BY DriveLetter) AS RowNo, DriveLetter, MB_Free FROM @DriveLetterInfo;
SELECT @p_RecordCounts = COUNT(*) FROM @DriveLetterInfoID;
print(@p_RecordCounts);
WHILE @p_RecordCounts != 0
BEGIN
SELECT @p_Drive = DriveLetter FROM @DriveLetterInfoID WHERE ID = @p_RecordCounts;
SET @p_SQL_Query = 'EXEC Master..xp_cmdshell ''fsutil volume diskfree ' + @p_Drive + ':'''
--2
PRINT @p_SQL_Query;
-- RETURN;
INSERT INTO @DriveDetails
EXEC (@p_SQL_Query);
--3
PRINT @p_SQL_Query;
--select * from DriveDetailsFinal
INSERT INTO @DriveDetailsFinal
SELECT
@p_Drive AS Drive,
CAST((((CAST(A.TotalBytes AS DECIMAL(18,2))/1024)/1024)/1024) AS DECIMAL(18,2)) AS 'Total Size (GB)',
CAST((((CAST(A.FreeBytes AS DECIMAL(18,2))/1024)/1024)/1024) AS DECIMAL(18,2)) AS 'Free Size (GB)',
CAST(((((CAST(A.TotalBytes AS DECIMAL(18,2)) - CAST(A.FreeBytes AS DECIMAL(18,2)))/1024)/1024)/1024) AS DECIMAL(18,2)) AS 'Used Size (GB)'
FROM
(
SELECT
SUM(CASE WHEN SizeDetails LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(SizeDetails, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN SizeDetails LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(SizeDetails, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN SizeDetails LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(SizeDetails, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM
(
SELECT SizeDetails FROM @DriveDetails WHERE SizeDetails LIKE 'Total # of %'
) AS tmp
) AS A;
-- DELETE FROM DriveDetails;
print(@p_RecordCounts)
SET @p_RecordCounts = @p_RecordCounts -1;
print(@p_RecordCounts)
END;
SELECT * FROM @DriveDetailsFinal ORDER BY Drive;
END;
