It does require that xp_cmdshell is turned ON. You can always turn it back OFF when you are finished.
Exec sp_configure 'show advanced options', 1
reconfigure
Exec sp_configure 'xp_cmdshell', 1
reconfigure
USAGE
After running something like one of these (below), then switch to the Messages tab.
Admin.dbo.FilesShow c
Admin.dbo.FilesShow 'C:\SSIS'
Copy/paste all the text from the Messages tab into a new query window. You now have some sample queries to get you started. I find GB/Path helpful.
HOW IT WORKS
Uses xp_cmdshell to run "dir /s" and places all the results into a global temp table which is then scrubbed into a usable form. The global temp table name is unique - anyone can run this same utility elsewhere and simultaneously without a conflict with table names.
NOTE
All dependencies are included below for these objects:
udfFormatInteger()
udfAddBackslashIfNec()
udfPadLeft()
Use Admin
IF Object_ID('dbo.udfFormatInteger') Is Not Null
DROP FUNCTION dbo.udfFormatInteger
go
CREATE FUNCTION dbo.udfFormatInteger(
@In bigint)
RETURNS varchar(20)
AS
/*
DATE AUTHOR REMARKS
8/19/15 PPaiva Initial creation.
DESCRIPTION
Formats a given integer with
commas.
Examples:
IN OUT
123 123
12345 12,345
-54321 -54,321
USAGE
SELECT
dbo.udfFormatInteger(1234567890)
SELECT dbo.udfFormatInteger(12345)
SELECT dbo.udfFormatInteger(-54321)
SELECT dbo.udfFormatInteger(0)
*/
BEGIN
DECLARE @Out varchar(20),
@sIn varchar(20),
@Balance varchar(20),
@CurrTextLen smallint,
@IsNegative bit
IF @In < 0
BEGIN
SET @IsNegative =
1
SET @In =
Abs(@In)
END
SET @sIn = Convert(varchar, @In)
SET @CurrTextLen = Len(@sIn)
SET @Out = ''
SET @Balance = @sIn
IF @CurrTextLen >
3
BEGIN
WHILE 1 =
1
BEGIN
SET @Out = ',' + Right(@Balance, 3) + @Out
SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)
SET @CurrTextLen = Len(@Balance)
IF @CurrTextLen >
3
CONTINUE
ELSE
BEGIN
SET @Out = @Balance + @Out
BREAK
END
END
END
ELSE
SET @Out = @sIn
IF @IsNegative = 1
SET @Out = '-' + @Out
RETURN @Out
END
go
IF Object_ID('dbo.udfAddBackslashIfNec') Is Not Null
DROP FUNCTION dbo.udfAddBackslashIfNec
GO
CREATE FUNCTION dbo.udfAddBackslashIfNec(
@In varchar(500)
)
RETURNS varchar(501)
AS
/* DATE AUTHOR REMARKS
8/19/15 PPaiva Initial creation.
USAGE
SELECT
dbo.udfAddBackslashIfNec('C:\blahblah')
SELECT
dbo.udfAddBackslashIfNec('C:\blahblah\')
DESCRIPTION
Returns
@In with a backslash at the right, if necessary.
*/
BEGIN
DECLARE @LenIn int,
@Out
varchar(201)
SET @LenIn = Len(@In)
IF Right(@In, 1) = '\' OR @In = ''
SET @Out = @In
ELSE
SET @Out = @In + '\'
RETURN @Out
END
go
IF Object_ID('dbo.udfPadLeft') Is Not Null
DROP FUNCTION dbo.udfPadLeft
go
CREATE FUNCTION dbo.udfPadLeft(
@In varchar(100),
@Width int,
@PadChar varchar(1)
)
RETURNS varchar(100)
AS
/*
DATE AUTHOR REMARKS
8/19/15 PPaiva Initial creation.
DESCRIPTION
Pads
@In with @PadChar so that the length of the returned
value
is @Width. If the length of @In is
greater than @Width
then
@In is returned.
USAGE
SELECT
dbo.udfPadLeft('7', '3', '0')
*/
BEGIN
DECLARE @Out varchar(100),
@LenIn int
SET @LenIn = Len(@In)
IF @Width - @LenIn < 0
SET @Out = @In
ELSE
SET @Out = REPLICATE(@PadChar, @Width - @LenIn) + @In
RETURN @Out
END
go
IF Object_ID('dbo.FilesShow') Is Not Null
DROP PROC dbo.FilesShow
go
create PROC dbo.FilesShow
@DriveLetterOrPath
varchar(200),
@AppendFiles
bit = 0,
@SuppressOutput
bit = 0,
@QueryHelp1
varchar(Max)= '' OUTPUT,
@QueryHelp2
varchar(Max)= '' OUTPUT,
@GlobalTableName
varchar(200) = '' OUTPUT,
@Debug bit = 0
AS
/* DATE AUTHOR REMARKS
2/28/16 PPaiva Initial creation.
DESCRIPTION
Populates
a global temp table with file info for all files
on
a given drive letter or path.
A
global temp table is used so the results can be further
refined.
After
running proc, switch to Messages pane to get SQL code
for
further analysis.
To
query for files on all drives, see proc FilesShowAll.
USAGE
Admin.dbo.FilesShow
c
Admin.dbo.FilesShow
'C:\Paul'
Admin.dbo.FilesShow
'\\data3\backup\SQLBackup\DB2'
Admin.dbo.FilesShow
'\\MachineName\MyFileshare'
To
for files on more than one drive letter, use @AppendFiles.
For
example, to include files on drives r, s, and t, run:
Exec
Admin.dbo.FilesShow r
Exec
Admin.dbo.FilesShow s, 1
Exec
Admin.dbo.FilesShow t, 1
DEPENDENCIES
udfFormatInteger()
udfAddBackslashIfNec()
udfPadLeft()
DEBUG
xp_fixeddrives
xp_cmdshell
'dir j:\ /s /-C /Ogn'
SELECT
Convert(varchar, GetDate(), 120)
*/
SET NOCOUNT ON
DECLARE @Cmd varchar(4000),
@Drive
char(1)
IF Len(@DriveLetterOrPath) = 1
SET @Cmd = 'dir /s /-C /Ogn ' +
@DriveLetterOrPath + ':\
'
ELSE
SET @Cmd = 'dir /s /-C /Ogn ' +
@DriveLetterOrPath
SET @Drive = Left(@DriveLetterOrPath,
1)
IF UPPER(@Drive) >= 'A' AND @Drive <= 'Z'
SELECT ''
ELSE
SET @Drive = '-'
-- Validate drive letter
CREATE TABLE #Drive (
Drive varchar(1),
MBFree char(50)
)
INSERT INTO #Drive
Exec master..xp_fixeddrives
IF @Debug = 1
SELECT *
FROM #Drive
IF Len(@DriveLetterOrPath) = 1
BEGIN
IF Not Exists( SELECT *
FROM #Drive
WHERE Drive =
@DriveLetterOrPath
)
BEGIN
SELECT ' No ' + Upper(@DriveLetterOrPath) + ': drive found. Acceptable
Drives are:' UNION
SELECT Drive
FROM #Drive
RETURN
END
END
DECLARE @s varchar(Max)
-- Generate global table name
SET @GlobalTableName = '##FilesShow_'
+ Substring(system_user, CHARINDEX( '\', SYSTEM_USER) + 1, 100)
+ '_'
+ Convert(varchar, GetDate(), 120)
SET @GlobalTableName = REPLACE(@GlobalTableName, '.', '_')
SET @GlobalTableName = REPLACE(@GlobalTableName, ' ', '_')
SET @GlobalTableName = REPLACE(@GlobalTableName, ':', '')
SET @GlobalTableName = REPLACE(@GlobalTableName, '-', '')
IF @Debug = 1
Print 'Global table name = '
+ @GlobalTableName
IF @AppendFiles = 0
-- Do not append, therefore need to drop the table
BEGIN
SET @s = 'IF Object_ID(''tempdb.dbo.' +
@GlobalTableName + ''')
Is Not Null
DROP
TABLE ' + @GlobalTableName
IF @Debug = 1
Print @s
Exec(@s)
END
ELSE
-- Append - no need to drop, but need to guard against
re-running for the same drive
BEGIN
SET @s = 'IF Object_ID(''tempdb.dbo.' +
@GlobalTableName + ''')
Is Not Null
DELETE
FROM ' + @GlobalTableName + ' WHERE Drive = ''' +
@Drive + ''''
IF @Debug = 1
Print @s
Exec(@s)
END
-- Always create the table if it does
not exist
SET @s = '
IF Object_ID(''tempdb.dbo.' +
@GlobalTableName + ''')
Is Null
CREATE
TABLE ' + @GlobalTableName + '(
ID
int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Drive
char(1),
FreeGBytesFMT
char(15),
SizeBytesFMT
char(35),
SizeGBytes
decimal(10, 1),
Name
varchar(1000),
Date
datetime,
PathName
varchar(2000),
Path
varchar(1000),
FreeMBytes
int,
SizeBytes
bigint,
DeleteScript
varchar(2000),
IsRecycleBin
bit DEFAULT 0,
Raw
varchar(2000)
)'
IF @Debug = 1
Print @s
Exec(@s)
SET @s = '
INSERT INTO ' +
@GlobalTableName + '
(Raw)
Exec
xp_cmdshell ''' + @Cmd + '''
-- For debugging
--SELECT
--charindex(''dir(s)'', Raw) ''Pos "dir(s)"'',
-- --charindex(''bytes
free'', Raw) ''Pos "bytes free"'',
-- Replace(substring(Raw,
charindex(''dir(s)'', Raw) + 7, charindex(''bytes free'', Raw) - 7 -
charindex(''dir(s)'', Raw)), '','', '''')
--FROM ##FilesShow_PaulLocal
--WHERE Raw Like ''%bytes free%''
UPDATE ' + @GlobalTableName
+ '
SET FreeMBytes = Convert(bigint,
Replace(substring(Raw,
charindex(''dir(s)'', Raw) + 7, charindex(''bytes free'', Raw) - 7 -
charindex(''dir(s)'', Raw)), '','', '''')
)
/1000000
FROM ' + @GlobalTableName + '
WHERE Raw Like ''%bytes free%''
UPDATE ' + @GlobalTableName
+ '
SET FreeMBytes = (SELECT Max(FreeMBytes)
FROM ' + @GlobalTableName + ')
DELETE
FROM ' + @GlobalTableName + '
WHERE Left(Raw, 2) = '' ''
OR Left(Raw, 4) = '' Vol''
OR Raw Is Null
OR Raw Like ''%<%''
UPDATE ' + @GlobalTableName
+ '
SET Date = Convert(datetime, Left(Raw,
20)),
SizeBytes
= Convert(bigint, Substring(Raw, 21, 18)),
SizeBytesFMT
= Admin.dbo.udfPadLeft(Convert(varchar(30),
Admin.dbo.udfFormatInteger(Convert(bigint, Substring(Raw, 21, 18)))), 15, ''
''),
SizeGBytes
= Convert(bigint, Substring(Raw, 21, 18)) / 1000000000.0,
Name
= Substring(Raw, 40, 1000)
WHERE Left(Raw, 13) <> '' Directory
of''
AND Left(Raw, 1) Like ''[0-9]''
UPDATE ' + @GlobalTableName
+ '
SET Path = Substring(Raw, 15, 1000)
WHERE Left(Raw, 13) = '' Directory of''
'
IF @Debug = 1
Print @s
Exec(@s)
SET @s = 'DECLARE @ID int,
@MaxID
int,
@PrevPath
varchar(1000),
@Path
varchar(1000)
SELECT
@MaxID = Max(ID),
@ID
= 0
FROM ' + @GlobalTableName + '
WHILE @ID < @MaxID
BEGIN
--
Get Previous Path
SELECT
@PrevPath = Path
FROM
' + @GlobalTableName + '
WHERE
ID = @ID
--
Get next row
SELECT
@ID = Min(ID)
FROM
' + @GlobalTableName + '
WHERE
ID > @ID
--
Get corresponding Path
SELECT
@Path = Path
FROM
' + @GlobalTableName + '
WHERE
ID = @ID
IF
@Path Is Null AND @PrevPath Is Not Null
UPDATE
' + @GlobalTableName + '
SET
Path = @PrevPath
WHERE
ID = @ID
END
'
IF @Debug = 1
Print @s
Exec(@s)
SET @s = '
DELETE
FROM ' + @GlobalTableName + '
WHERE Date Is Null
UPDATE ' + @GlobalTableName
+ '
SET PathName =
dbo.udfAddBackslashIfNec(Path) + Name
UPDATE ' + @GlobalTableName
+ '
SET DeleteScript = ''Exec xp_cmdshell
''''del "'' + PathName + ''"''''''
UPDATE ' + @GlobalTableName
+ '
SET IsRecycleBin = 1
WHERE PathName Like ''' +
@DriveLetterOrPath + ':\$Recycle.Bin\%''
OR PathName Like ''' + @DriveLetterOrPath + ':\Recycle%''
UPDATE ' + @GlobalTableName
+ '
SET Drive = ''' +
@Drive + '''
-- Total bytes free
UPDATE ' + @GlobalTableName
+ '
SET FreeGBytesFMT =
Admin.dbo.udfFormatInteger((FreeMBytes/1000.))'
IF @Debug = 1
Print @s
IF @Debug = 1
SELECT
@GlobalTableName '@GlobalTablename',
@DriveLetterOrPath
'@DriveLetterOfPath',
@Drive
'@Drive'
Exec(@s)
-- Optional indexes
--SET @s = 'CREATE INDEX idx_Name_' +
@GlobalTableName + ' ON ' + @GlobalTableName + '(Name)
--CREATE INDEX idx_SizeBytes_' +
@GlobalTableName + ' ON ' + @GlobalTableName + '(SizeBytes)
--CREATE INDEX idx_PathName_' +
@GlobalTableName + ' ON ' + @GlobalTableName + '(PathName)
--'
--IF @Debug = 1
-- Print
@s
--Exec(@s)
SET @QueryHelp1 = 'SELECT Drive,
Count(*)
NumFiles,
Sum(SizeGBytes)
UsedGBytes,
Min(FreeMBytes/1000)
FreeGBytes
FROM ' + @GlobalTableName + '
GROUP BY Drive
ORDER BY Drive
SELECT *
FROM ' + @GlobalTableName + '
'
IF @SuppressOutput = 0
BEGIN
IF @Debug = 1
Print @QueryHelp1
Exec(@QueryHelp1)
END
SET @QueryHelp2 = '
-- Show all files, sort by largest
SELECT TOP 1000 *
FROM ' + @GlobalTableName + '
WHERE Name Like ''%%''
ORDER BY SizeBytes desc
-- Count of all files by Drive
SELECT
Drive,
Count(*)
NumFiles,
Sum(SizeGBytes)
UsedGBytes,
Min(FreeMBytes/1000)
FreeGBytes
FROM ' + @GlobalTableName + '
GROUP BY Drive
ORDER BY Drive
-- Count of all files, all drives
SELECT Count(*) NumFilesAllDrives,
Sum(SizeGBytes) TotalGBytesAddDrives
FROM ' + @GlobalTableName + '
-- GB/Path
SELECT Path, Convert(Decimal(5, 1),
Sum(SizeBytes/1000000000.0)) SizeGB
FROM ' + @GlobalTableName + '
GROUP BY Path
HAVING Sum(SizeBytes/1000000000.0) >=
.25
ORDER BY 2 desc
-- Files in Recycle Bin
SELECT *
FROM ' + @GlobalTableName + '
WHERE IsRecycleBin = 1
ORDER BY SizeBytes desc
-- Sum of Files in Recyle Bin
SELECT
Admin.dbo.udfFormatInteger(Sum(SizeBytes)) SumBytesInRecycleBin
FROM ' + @GlobalTableName + '
WHERE IsRecycleBin = 1
-- Show most recent files
SELECT TOP 1000 *
FROM ' + @GlobalTableName + '
ORDER BY Date desc
'
IF @SuppressOutput = 0
Print @QueryHelp2
