create PROC procname
@drive CHAR(1)
AS
SET NOCOUNT ON
DECLARE @dynamic_sql VARCHAR(1000)
SET @dynamic_sql = CONCAT(‘EXEC xp_cmdshell ”dir ‘,@drive,’:\ /a /s”’)
PRINT @dynamic_sql
CREATE TABLE #t (id INT IDENTITY(1,1), output VARCHAR(MAX))
INSERT INTO #t
EXEC (@dynamic_sql)
DELETE FROM #t WHERE output IS NULL
DELETE FROM #t WHERE output LIKE ‘%<DIR>%’
DELETE FROM #t WHERE output LIKE ‘%file%bytes’
DELETE FROM #t WHERE output LIKE ‘%dir%bytes’
DELETE FROM #t WHERE output LIKE ‘%dir%bytes’
DELETE FROM #t WHERE output =’ Total Files Listed:’
DELETE FROM #t WHERE output LIKE ‘%Volume in drive%’
DELETE FROM #t WHERE output LIKE ‘%Volume Serial Number%’
DELETE FROM #t WHERE output LIKE ‘%Dir%bytes free’
DELETE FROM #t WHERE output LIKE ‘%The directory name%’
DELETE FROM #t WHERE output LIKE ‘%is too long%’
DELETE FROM #t WHERE output LIKE ‘%<JUNCTION>%’
DELETE FROM #t WHERE output LIKE ‘%<SYMLINKD>%’
ALTER TABLE #t ADD data_type int
UPDATE #t SET data_type = 0 WHERE output LIKE ‘ Directory%’
UPDATE #t SET data_type = 1 WHERE data_type IS NULL
UPDATE #t SET output = LTRIM(SUBSTRING(output,18,1000)) FROM #t WHERE data_type = 1
ALTER TABLE #t ADD first_space INT
UPDATE #t SET first_space = CHARINDEX(‘ ‘,output) FROM #t WHERE data_type = 1
DELETE FROM #t WHERE first_space = 0 AND data_type = 1
ALTER TABLE #t ADD size BIGINT,filename VARCHAR(1000)
UPDATE #t SET SIZE = REPLACE(SUBSTRING(output,1,first_space-1),’,’,”),filename = SUBSTRING(output,first_space+1,1000) FROM #t WHERE data_type =1
CREATE clustered INDEX i ON #t (id)
ALTER TABLE #t ADD directory VARCHAR(1000)
DECLARE @row BIGINT = 1,@max BIGINT
SELECT @max = MAX(id) FROM #t
WHILE @row <= @max
BEGIN
DECLARE @data_type INT, @outputvalue VARCHAR(1000), @directory VARCHAR(1000)
SELECT @data_type = data_type,@outputvalue = output FROM #t WHERE id = @row
IF @data_type = 0 SET @directory = @outputvalue
IF @data_type = 1
UPDATE #t SET directory = REPLACE(@directory,’ Directory of ‘,”) FROM #t WHERE id = @row
SET @row +=1
end
DELETE FROM #t WHERE data_type = 0
IF (SELECT OBJECT_ID(‘table’)) IS NULL
EXEC (‘SELECT TOP 0 filename,directory,size,GETDATE() capture_time INTO table FROM #t ‘)
INSERT INTO table
SELECT filename,directory,size,GETDATE() capture_time FROM #t ORDER BY size DESC
SQL Notes 