Jeremy Carter

All too often there are things I would like to do outside of SQL Server as part of a job or automation task and I would really prefer not to use xp_cmdshell. Really thats goes for most of the extended procedures as they tend to either not be supported or just don’t have the flexibility or error handling I need. Therefore I set out to design a collection of SQL CLR stored procedures and functions to help with my everyday file and directory operation needs. It also includes #ziplib to zip/unzip file and folders.

--1.) CopyFile
declare @result int
exec @result = clrFile.CopyFile 'c:\temp\test1.txt','c:\temp\test2.txt' --will not overwrite if the destination exists
select @result --returns a 0 if successful or a 1 if failed, mimics @@error

exec @result = clrFile.CopyFile 'c:\temp\test1.txt','c:\temp\test2.txt', true --*CAUTION* This will overwrite an existing file
select @result --returns a 0 if successful or a 1 if failed, mimics @@error

--2.) DeleteFile
declare @result int
exec @result = clrFile.DeleteFile 'c:\temp\test2.txt'
select @result --returns a 0 if successful or a 1 if failed, mimics @@error

--3.) MoveFile
declare @result int
exec @result = clrFile.MoveFile 'c:\temp\test1.txt','c:\temp\test2.txt'
select @result --returns a 0 if successful or a 1 if failed, mimics @@error

--4.) DoesFileExist
select physical_name, clrFile.DoesFileExist(physical_name) FROM sys.master_files

--Returns a 1 for true, 0 for false

--5.) cmdDir
select * from  clrFile.cmdDir('c:\temp','') --returns a table of all subdirectories and files

select * from  clrFile.cmdDir('c:\temp','*.txt') -- returns a table with filtered results

--6.) GetDirectoryName
select clrFile.GetDirectoryName(physical_name) AS [DirectoryName]
FROM sys.master_files WHERE database_id=1 and type=0

--returns
--'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\DATA'

--7.) GetFilename
select clrFile.GetFileName(physical_name) AS [FileName]
FROM sys.master_files WHERE database_id=1 and type=0

--returns
--'master.mdf'

--8.) GetFileExtension
select clrFile.GetFileExtension(physical_name) AS [Exentsion]
FROM sys.master_files WHERE database_id=1 and type=0

--returns
--'.mdf'

--OR Combine 6 & 7
select clrFile.GetDirectoryName(physical_name) AS [DirectoryName],
clrFile.GetFilename(physical_name) AS [Filename]
FROM sys.master_files WHERE database_id=1 and type=0

--returns
--DirectoryName, Filename
--C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\DATA, master.mdf

--9.) CompressFile
--the compressed filename will be filename.zip unless youspecify something different
declare @result int
exec @result = clrFile.CompressFile 'c:\temp\dbbackup.bak'
select @result --returns a 0 for success or a 1 if failed, mimics @@error

declare @result int
exec @result = clrFile.CompressFile 'c:\temp\dbbackup.bak', 'c:\anotherfolder\dbbackup.zip'
select @result --returns a 0 for success or a 1 if failed, mimics @@error

--it will also compress a folder
declare @result int
exec @result = clrFile.CompressFile 'c:\temp\somefolder'
select @result --returns a 0 for success or a 1 if failed, mimics @@error

--10.) DecompressFile
declare @result int
exec @result = clrFile.DecompressFile 'c:\temp\dbbackup.bak.gz'
select @result --returns a 0 for success or a 1 if failed, mimics @@error

--11.) CreateDirectory
declare @result int
msdb.clrFile.CreateDirectory 'c:\temp\newdirectory'
select @result --returns a 0 for success or a 1 if failed, mimics @@error

--12.) DeleteDirectory
declare @result int
msdb.clrFile.DeleteDirectory 'c:\temp\newdirectory'
select @result --returns a 0 for success or a 1 if failed, mimics @@error

--13.) MoveDirectory
declare @result int
msdb.clrFile.MoveDirectory 'c:\temp\openssl', 'c:\temp\openssl2'
select @result --returns a 0 for success or a 1 if failed, mimics @@error

--14.) CopyDirectory
declare @result int
msdb.clrFile.CopyDirectory 'c:\temp\openssl2', 'c:\temp\openssl',1
select @result --returns a 0 for success or a 1 if failed, mimics @@error

--15.) PathCombine
declare @combined varchar(max)= msdb.clrFile.PathCombine('c:\temp','test.txt')
select @combined

--16.) GetRandomFile
declare @randomfile varchar(max) = msdb.clrFile.GetRandomFilename()
select @randomfile

--17.) DoesDirectoryExist
select db_name(database_id) as database_name, physical_name, msdb.clrFile.DoesDirectoryExist(physical_name)as is_directory from sys.master_files

--18.) GetDrveSpace
select * from msdb.clrFile.GetDriveSpace()

--returns
--DriveLetter VolumeLabel TotalSize FreeSpace SpaceUsed
--all numbers are in MB.

Current Version: 1.2.1.1
Release Date: 2010-03-02