Home Forums SQL Server 2005 T-SQL (SS2K5) Scripting help - Need a query to find filenames and filepaths under a certain directory RE: Scripting help - Need a query to find filenames and filepaths under a certain directory

  • October 27, 2009 at 3:42 pm

    #1071484

    here's something to get you started. Assuming you've enabled xp_cmdshell, and the SQL service is using credentials that can see the program files folder, this command would give you a detailed list of all files and sub-folders.

    go to the command prompt and do dir /? to see other options, like if you want the /b for brief results with no sizes/dates. review the results, it should be obvious how you can use substring() to get the data elements out of it.

    note this command took more than 35 minutes to run on my laptops 5400rpm hard drive to return 324,570 rows of results. it expanded my tempdb by a little more than 30 meg.

    you need to make sure this is a one time thing, and not something you do regularly.

    CREATE TABLE #Results (txtResult varchar(2000) NULL)

    INSERT INTO #Results (txtResult)

    EXEC master.dbo.xp_cmdshell 'DIR "C:\PROGRAM FILES\*.*" /s'

    SELECT * FROM #Results

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!