I'm trying to find a way to list all folders, and subfolders, in a directory. I ran the code below and got a message that says 'Access is Denied'. There is no way access is denied to that folder. How can I list all folders, and subfolders, in
a directory?
--'C:\Users\Ryan\Desktop\Coding\';
set nocount on
declare @curdir nvarchar(400)
declare @line varchar(400)
declare @command varchar(400)
declare @counter int
If (select count (*) from sys.objects where name='Output') <> 0 DROP TABLE output
If (select count (*) from tempdb.sys.objects where name like '%#Tempoutput%') <> 0 DROP TABLE #Tempoutput
If (select count (*) from tempdb.sys.objects where name like '%#dirs%') <> 0 DROP TABLE #dirs
create table #dirs (DIRID int identity(1,1), directory varchar(400))
Set @command = 'dir "C:\Users\Ryan\Desktop\Coding\Microsoft Access\" /S/O/B/A:D'
insert into #dirs exec xp_cmdshell @command
set @counter = (select count(*) from #dirs)
create table #tempoutput (line varchar(400))
create table output (Directory varchar(400), FileSize varchar(400))
While @Counter <> 0
Begin
Declare @filesize int
set @curdir = (select directory from #dirs where DIRID = @counter)
set @command = 'dir "' + @curdir +'"'
insert into #tempoutput
exec master.dbo.xp_cmdshell @command
select @line = ltrim(replace(substring(line, charindex(')', line)+1,len(line)), ',', ''))
from #tempoutput where line like '%File(s)%bytes'
Set @filesize = Replace(@line, ' bytes', '')
Insert into output (directory, Filesize) values (@curdir, @filesize)
Set @counter = @counter -1
End
Delete from output where Directory is null
select * from output
Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.
