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).
Who is Participating?
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 :)
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?
or master.dbo.xp_cmdshell
INSERT Table EXECUTE @Return = master.dbo.xp_cmdshell  'DIR C:\Program Files\'
melinaltAuthor Commented:
Thanks a lot.. It did the trick
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:

@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

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.