Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Accessing the Windows File System from SQL Server


By:   |   Read Comments (4)   |   Related Tips: More > Security

Problem
Within my SQL Server T-SQL stored procedures and scripts I need to find out the files in a specific directory.  What are some approaches to do so?  I need some flexibility to capture the file names and general information for subsequent processing.  What are the native SQL Server options as well as the custom options that are available?

Solution
Depending on the exact needs dictates the command(s) that need to be issued from SQL Server 2000 or 2005.  Below outlines some options to access the Windows file system related information with both native and custom code.  In general the following commands exist:

Extended Stored Procedure - xp_cmdshell - Ability to execute any DOS command line code.
(Platform = SQL Server 2000 and SQL Server 2005)

EXEC master.dbo.xp_cmdshell 'dir c:\'
GO 

Extended Stored Procedure - xp_fixeddrives - Ability to capture the free drive space in megabytes.
(Platform = SQL Server 2000 and SQL Server 2005)

EXEC master.dbo.xp_fixeddrives
GO
 

Extended Stored Procedure - xp_subdirs - Ability to capture the sub directories, but not files.
(Platform = SQL Server 2000 and SQL Server 2005)

EXEC master.dbo.xp_subdirs 'c:\'
GO
 

Custom Code - snippet - Ability to capture the file names in a temporary table with xp_cmdshell.
(Platform = SQL Server 2000 and SQL Server 2005)

/*
----------------------------------------------------------------------------
-- Object Name: Script
-- Project: Misc
-- Business Process: Supports SQL Server 2000 and 2005
-- Purpose: Capture the files in a specific directory with xp_cmdshell
-- Database: N\A
-- Dependent Objects:
-- Called By: N\A
-- Upstream Systems: N\A
-- Downstream Systems: N\A
--
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
--
*/

SET NOCOUNT ON

-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000)
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(200
)

-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000)
NULL
)

-- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list
CREATE TABLE #ParsedFileList (
PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
DateTimeStamp datetime NOT NULL,
FileSize varchar(50) NOT NULL,
FileName1 varchar (255) NOT
NULL
)

-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = ''
SELECT @FilePath =
'C:\Progra~1\Micros~2\MSSQL.1\MSSQL\Log\'

-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.*' + char(39)

-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' +
@CMD1

-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)

-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList
WHERE COL1 IS
NULL

DELETE FROM #OriginalFileList
WHERE COL1 LIKE
'%Volume%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%<DIR>%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE
'%bytes%'

-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM
#OriginalFileList

-- ********************************************************************************
-- INSERT code here to process the data from the
#ParsedFileList table
-- ********************************************************************************

-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE
#ParsedFileList

SET NOCOUNT OFF
GO

 

Next Steps

  • Keep in mind that the master.dbo.xp_cmdshell extended stored procedure will permit you to execute any Windows command just as if you were in a DOS session.  So you need to review the capabilities of this extended stored procedure from a security perspective to determine the impacts in your environment.
  • The master.dbo.xp_cmdshell extended stored procedure is available in both SQL Server 2000 and 2005.  In SQL Server 2005 the master.dbo.xp_cmdshell extended stored procedure is off by default and needs to be enabled via the Surface Area Configuration Manager.
  • For more information about securing the master.dbo.xp_cmdshell extended stored procedure, check out - <-- Security Bulletin --> How do I secure an extended stored procedure?


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, March 08, 2013 - 12:11:12 AM - Jeremy Kadlec Back To Top

Bob,

Thank you for the feedback and happy to help!

Thank you,
Jeremy Kadlec
Community Co-Leader


Wednesday, March 06, 2013 - 2:14:59 PM - Bob Harrsion Back To Top

Thanks for this post, it was a great help for a starting point. HOWEVER, it will skip any files that have the strings 'Directory', 'Volume', or 'bytes' in the filename. So when I use this, I will check for those strings in the location that would indicate it is not a record with a filename. Again thanks, this is a great help if you make the little twicks to it.

Bob


Monday, January 04, 2010 - 4:19:15 PM - admin Back To Top

PhilHege,

Thank you for alternative approach.

Thank you,
The MSSQLTips Team


Monday, January 04, 2010 - 3:17:54 PM - philhege Back To Top

In many environments, security concerns prevent the enabling of xp_cmdshell.  So how would one get the information without it?

If you can rely on a scheduled job, here's a solution:

Create a job with a job step that uses the Operating system (CMDEXEC) type.  Create a DOS command in this step that pipes the results of a DIR to a file:

DIR d:\MyDirectory >d:\MyOutputFolder\MyDirectoryList.txt

Next, create a job step that BULK INSERTs the contents of the file to a temporary table.  From this point, you can evaluate the contents of the directory.  In this example, I look for the existence of a certain file, and return an error code if it doesn't exist.  The job is set up to quit with success if this step fails (returning the error from RAISERROR), so it doesn't report a job failure. (In this case, I simply want to stop processing the job if the file's not there.)  

declare @sql varchar(256)

create table #filelist (fileinfo varchar(255))

set @sql = 'BULK INSERT #filelist

from ' + '''d:\MyOutputFolder\MyDirectoryList.txt''' + '

with (DataFileType = '+ '''char''' + ', FieldTerminator = ' + ''',''' + ',

FirstRow = 1)'

exec(@sql)

--Look for the file name in the temp table. Each record is an entry from the DIR command results.

if NOT EXISTS(select * from #filelist where charindex('MyFile.txt',fileinfo) > 0)

BEGIN

DROP TABLE #filelist

RAISERROR('MyFile.txt not found.',16,1)

END

ELSE

DROP TABLE #filelist

 

You can do whatever you want with the data once it's in the temporary table. Issue a DROP TABLE #filelist at the end of your code to clean up.

 

 

 

 


Learn more about SQL Server tools