Rename files in a folder using T-SQL (generic)

SCENARIO
You have downloaded 50 files into a folder from somewhere like Facebook, and the files have gobbledygook names.  You want to rename the files with a base name + a number.

For example:
25488106_959991_6827072580628_o.jpg  >> FredAndWilma01.jpg
25488256_959951_1851503523073_o.jpg  >> FredAndWilma02.jpg

Here is a code snippet which I keep in a proc.  It simply generates the rename code that can be run from a command window, a batch file, or as I have done below, right here in T-SQL provided that you turn on xp_cmdshell.  For those that are security conscious, you can turn this on temporarily.

Use Admin

go

IF OBJECT_ID('dbo.cameraFileRenumberGeneric') Is Not Null

       DROP PROC dbo.cameraFileRenumberGeneric

GO

CREATE PROC dbo.cameraFileRenumberGeneric

AS

/*     DATE          AUTHOR        REMARKS

       1/1/17        PPaiva        Initial creation.

       DESCRIPTION

              Renumbers camera files with a numeric offset.

              Generates rename code for xp_cmdshell.

       SCENARIO

              You want to rename all files in a folder with a base name + a number

              Example:      25488106_959951922091_6828033757072580628_o.jpg  >> FredAndWilma01.jpg

                                   25488256_959965365151_1853783911503523073_o.jpg  >> FredAndWilma02.jpg

                                 

*/

RETURN -- This is a code snippet, not meant to run

-- DROP TABLE #t

CREATE TABLE #t(

       ID int NOT NULL IDENTITY(1, 1),

       Filename varchar(100)

       )

INSERT INTO #t (Filename)

       exec xp_cmdshell 'dir /b C:\Paul\CoCreativeJourneys\Clients\Past\2017-07-08_FredAndWilma\Images'

DELETE #t

WHERE Filename Is Null

SELECT *,              

              'Exec xp_cmdshell ''rename "C:\Paul\CoCreativeJourneys\Clients\Past\2017-07-08_FredAndWilma\Images\'

              + Filename + '" FredAndWilma'

              + dbo.udfPadLeft(ID, 2, '0') + '.jpg'''

FROM #t