SQL does UNC and local disk paths for FileName, FilePath, FolderPath

I frequently find my self needing to ensure my config tables are in sync with a Folder some network share or local drive. Whipped up a clean script to break it down simply


SET NOCOUNT ON
DECLARE
@CMD VARCHAR(500)
, @FolderPath VARCHAR(256)
, @FileExt VARCHAR(5) --// 'txt'
, @Subdirectories VARCHAR(3)

SELECT
--// UNC path may work too
@FolderPath = 'C:\Program Files\7-zip\' --// *include trailing back slash
, @FileExt = 'exe'
, @Subdirectories = 'YES' --// 'YES' | '' / 'NO'

SELECT
@CMD = 'dir /B /Aa'
+ CASE WHEN ISNULL(@Subdirectories, '') = 'YES' THEN '/S'
ELSE ''
END + ' ' + '"' + @FolderPath + CASE WHEN LEN(@FileExt) >= 1
THEN '*.' + @FileExt
ELSE ''
END + '"'
PRINT @CMD

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

CREATE TABLE #tmp (filePath VARCHAR(2000))

INSERT INTO #tmp
EXECUTE master.dbo.xp_cmdshell @CMD ;
--//Used CTE to provide some flexibility. Typically when looking for files
--// I need to compare configuration info like folder path or files in a
--// Config table. This allows for clean Joins.
WITH CTE
AS (
SELECT
CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
THEN @FolderPath + filePath
ELSE filePath
END [FilePath]
, CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
THEN @FolderPath
ELSE LEFT(filePath,
LEN(filepath) - CHARINDEX('\',
REVERSE(filePath))
+ 1)
END [FolderPath]
, CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
THEN filePath
ELSE RIGHT(filePath,
CHARINDEX('\', REVERSE(filePath)) - 1)
END [FileName]
FROM
#tmp
WHERE
LEN(ISNULL(filePath, '')) > 0
)
SELECT
*
FROM
CTE




~Enjoy

Comments :

0 comments to “SQL does UNC and local disk paths for FileName, FilePath, FolderPath”