SQL-Articles

I’m writing this script since most of them are looking a way to find total diskspace available in a drives through sql server. I hope there is no extended procedure for this. I’ve used WMI script to do this, download the vbs script and save it to a location and use this location in the sql script to get the result.

SQL Script

SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME =‘##tmp’)
DROP TABLE ##tmp
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell ‘cscript C:\diskspace.vbs’ – change the path here
SET ROWCOUNT 3
DELETE ##tmp
SET ROWCOUNT 0
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME =‘##tmp2′)
DROP TABLE ##tmp2
CREATE TABLE ##tmp2(Driveletter VARCHAR(2),TotalDiskSpace_in_MB FLOAT, Freespace_in_MB FLOAT)
INSERT ##tmp2
SELECT SUBSTRING(diskspace,1,3) , CONVERT(FLOAT,SUBSTRING(diskspace,4,10)),
CONVERT(FLOAT,SUBSTRING(diskspace,15,10)) FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2

Sample Output

Download diskspace.vbs

Scripts, T-SQL, Uncategorized Diskspace, SQL Server, T-SQL