get the contents of a file system

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: