Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

T-SQL Statement to get file list? 1

Status
Not open for further replies.

gbaughma

IS-IT--Management
Staff member
Joined
Nov 21, 2003
Messages
4,773
Location
US
I have a server with 30,000+ files that I need to be able to quickly search through.

So I figured I'd just write a stored procedure to get the list of files (and their sub-directories)... however I've run into a small snag.

I have an index, a field called FilePath and one called FileName. The FilePath should be the complete path to the file (i.e. "\\server\share\filename.pdf") and the Filename should be just "filename.pdf"

I figured out I can do:
Code:
INSERT INTO tblFileList (FilePath)
   EXEC xp_cmdshell 'dir \\server\share\ /S /B'

... which loads up the filepath. Is there a way to (at the same time) load the filename into the other field as well?

(I suppose in a pinch, a stored procedure that then parses the filename off the end and updates the records would be OK as well)...

TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Hehe... never mind... Reading the Fine Manual helped out.....

Here's how I did it....

Code:
INSERT INTO tblFileList (FilePath)
  EXEC xp_cmdshell 'dir\\server\share /S /B'

... to fill the first one... then

Code:
UPDATE tblFileList 
   SET FileName = RIGHT(FilePath,CHARINDEX('\',REVERSE(FilePath))-1)

... just thought I'd share. :)


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Ha ha. I was just about to post that exact same thing. The only thing to be careful of.... if there is no backslash in your data, you will get an error. Ex:

Code:
Declare @Test VarChar(100)
Set @Test = 'filename.ext'
Select Right(@Test, CharIndex('\', Reverse(@Test))-1)

Since there is no backslash, charindex returns 0. you do the right of (Data, 0-1) which errors out.

You can accommodate that potential problem easily. Like this...

Code:
Declare @Test VarChar(100)
Set @Test = 'filename.ext'
Select Right(@Test, CharIndex('\', Reverse([!]'\' + [/!] @Test))-1)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Mmm... good plan.

Well, I wrote a stored procedure and scheduled it to run every 2 hours during business hours.

I'll adjust the procedure as you suggest, since that's a good idea.

Ummm.... wait... if I do Reverse('\' + filepath) wouldn't that make it htapelif\, adding the \ at the wrong end?

Wouldn't I have to do '\' + Reverse(Filepath) to keep it at the correct end?

ARRRGH! Too early in the morning to do string concatenation in my head.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
Nope. It works. I tested it.

Code:
Declare @Test Table(FilePath VarChar(200))

Insert Into @Test Values('filename.ext')
Insert Into @Test Values('C:\filename.ext')
Insert Into @Test Values('C:\Folder\filename.ext')
Insert Into @Test Values('\\Server\share\Folder\filename.ext')
Insert Into @Test Values('')
Insert Into @Test Values(NULL)
Insert Into @Test Values('not really a file name')


Select FilePath, Right(FilePath, CharIndex('\', Reverse('\' + FilePath))-1)
From @Test

Copy paste the code shown above in to a query window and run it. Seems to work properly every time.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I tried to run it against a network directory and I get

"The system cannot find the path specified."

but I can run it against a local drive like C:.

I can browse the network directory in Explorer and I can run the dir command against it in a dos window.

Any thoughts?

Simi
 
Permissions

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Click Start -> Run
services.msc
Scroll down to the SQL Server service.
Right click -> Properties
Click Log On tab.

By default, SQL uses the Local System account. This account has permissions to everything (and every folder) on the computer, but no permissions to any other computers on the network. If you want to list files and folders on another computer, you need to use a domain account that has permissions to the other computer. When you change the Log On configuration, you will need to restart the sql service before your changes take affect.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top