Project Description
FileSystemHelper SQL Server CLR provides a collection of CLR stored procedures and functions for interacting with the file system. Using these stored procedures and functions will allow you to avoid enabling xp_cmdshell on your SQL Server instances.

The project creates the following functions and stored procedures:
  • Utility.DirectoryCreate Stored procedure that creates a directory based on the @Path value provided.
  • Utility.DirectoryDelete Stored procedure that deletes a directory based on the @Path value provided.
  • Utility.DirectoryDeleteContents Stored procedure that deletes the contents of a directory based on the @Path value provided.
  • Utility.DirectoryList Table valued user-defined function that accepts @Path and @Extension and returns information on the directory. Leaving the @Extension variable blank will return all files in the directory. The table returned provides Name, Directory, Size, DateCreated, DateModified, and Extension.
  • Utility.DirectoryMoveTo Stored procedure that moves the contents of one directory to another. The procedure accepts two variables @Path and @Destination. Where files at @Path are moved to @Destination.
  • Utility.DirectorySize Function that returns the total size in bytes of all files within a directory and its sub-directories.
  • Utility.FileCopy Stored procedure that copies files from one location to another. The procedure accepts two parameters @ExistingFileNamePath and @NewFileNamePath. The existing file is uses the @ExistingFileNamePath and the new file name is submitted with @NewFileNamePath. The procedure will overwrite existing file.
  • Utility.FileCreate Stored procedure that creates an empty file. The stored procedure accepts two parameters @Filename and @Path. The parameter @Filename accepts the name of the file created. The parameter @Path accpets the location where the file is created.
  • Utility.FileDelete Stored procedure that deletes a specified file. The parameter @Filename accepts the path and filename of the file to be deleted.
  • Utility.FileMove Stored procedure that moves a file from one location to another. The stored procedure accepts two parameters @Filename and @FileDestination. The parameter @Filename accepts the name of the existing file. The parameter @FileDestination accepts the location where the file is moved to.
  • Utility.FileRename Stored procedure that renames a file. The stored procedure accepts two parameters @FileNewName and @FileOldName. The parameter @FileNewName accepts the path and name of the new file name. The parameter @FileOldName accepts the path and name of the existing file.
  • Utility.FileSearch Stored procedure that searches a directory for a string pattern. The stored procedure accepts two parameters @Pattern and @Path. The @Pattern parameter determines the value to search for within the folder. The @Path defines the folder that the procedure will search.
  • Utility.FileSearchInternal Stored procedure that searches a file for a string pattern. The stored procedure accepts two parameters @Pattern and @Filename. The @Pattern parameter determines the value to search for within the folder. The @Filename defines the file that the procedure will search.

Note The security surrounding the installation of the CLR function and stored procedures follows best practices to the best of my knowledge.

Installation
To install FileSystemHelper you will need to execute the script FileSystemHelperInstall.sql. Thie script references the location the FileSystemHelper.dll which will be different on your machine.

Support
If you help or come across bugs please post a comment on this page or write an email. If it is a feature request or a bug report, you can use the "Issue Tracker" tab.

Last edited Feb 23, 2011 at 7:37 AM by StrateSQL, version 9