First, thank you to Jeff Moden for pulling out xp_dirtree, which is exactly what I was thinking when I saw the title.
With thanks to Jeff (and Robyn and Phil on Simple-Talk), let me post a minimal directory details snippet I copied from his post - functionally identical to both what I actually use, and fairly similar to the output of
dir /b path
and which has always been as much as I've needed SQL Server to do for me for handling backup files:
SET NOCOUNT ON
DECLARE @piFullPath VARCHAR(128)
DECLARE @DirTreeCount INT --Remembers number of rows for xp_DirTree
SET @piFullPath = '\\YourServer\YourShare\YourPath'
--SET @piFullPath = 'c:\temp'
IF OBJECT_ID('TempDB..#DirTree','U') IS NOT NULL
DROP TABLE #DirTree
CREATE TABLE #DirTree
RowNum INT IDENTITY(1,1),
Name VARCHAR(256) PRIMARY KEY CLUSTERED,
-- Get all the file names for the directory (includes directory names as IsFile = 0)
--===== Get the file names for the desired path
INSERT INTO #DirTree (Name, Depth, IsFile)
EXEC Master.dbo.xp_Dirtree @piFullPath,1,1 -- Current diretory only, list file names
-- Remember the row count
SET @DirTreeCount = @@ROWCOUNT
--===== Update the file names with the path for ease of processing later on
SET Name = @piFullPath + Name
SELECT * FROM #dirtree
P.S. If you're trying to do OS level work from SQL Server, I have to submit that you may be using the wrong tool. Go the other way - write OS level software (batch files, scripting language, executables) that either use something like sqlcmd to call the database, or that connect to your database directly.
P.P.S. If you folks are really going to get into email parsing, then at least follow a known standard, like RFC 822. Here's an example of a RegEx for RFC822 email address parsing: http://www.ex-parrot.com/pdw/Mail-RFC822-Address.html