Get list of files inside folder

I need to write a store procedure in SQL 2005 to run every night.
This procedure should go to a specific folder and obtain the name of each an all files existing inside the folder and/or subfolders (get each name, size, format, etc and add it to a table in my database).
LVL 1
melinaltAsked:
Who is Participating?
 
ErnariashCommented:
I will go with CLR Stored Procedures or SSIS but if all you have on is xp_cmdshell. This will give you a good start enjoy :)
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1002&lngWId=5
 
0
 
ErnariashCommented:
It seems that  you are going to need SSIS or CLR Stored Procedures. Please let me know if you have CLR on in your DB?
0
 
ErnariashCommented:
or master.dbo.xp_cmdshell
INSERT Table EXECUTE @Return = master.dbo.xp_cmdshell  'DIR C:\Program Files\'
 
0
 
melinaltAuthor Commented:
Thanks a lot.. It did the trick
0
 
melinaltAuthor Commented:
Thanks Ernariash....

Your help was great... I wrote a store procedure following your example and it worked perfectly.


Here is the entire code in case someone else needs it:


declare 
@Return int,
@Work varchar(2000),
@PCWrite varchar(2000)
 
SET @PCWrite = '\\myserver\'
SET @Work = 'DIR ' + '"' + @PCWrite + '"'
 
CREATE TABLE #Files ([ID] int IDENTITY (1, 1) NOT NULL,MyFile nvarchar(200))
 
INSERT #Files EXECUTE @Return = master.dbo.xp_cmdshell  @Work
 
'Clean up to get only the files (no directories or others)
DELETE #Files WHERE (MyFile is null) or (MyFile='') or (MyFile like '%Directory of%') or (MyFile like '%<DIR>%') or (MyFile like '%Volume%') or (MyFile like '%bytes free%') or (MyFile like '%file(s)%')
 
'List only the file name and type (without date/size)
SELECT SUBSTRING(MyFile,40,100)  as fName, (right(SUBSTRING(MyFile,40,100), (len(SUBSTRING(MyFile,40,100)) - charindex('.',SUBSTRING(MyFile,40,100))))) as fType FROM #Files order by myfile
 
drop table #Files

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.