About Me

Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present. 517 Lafarge Ave. Louisville, CO 80027 7667 Dyer Road Louisville, CO 80027

Sunday, March 19, 2017

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

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

IF OBJECT_ID('dbo.cameraFileRenumberGeneric') Is Not Null
       DROP PROC dbo.cameraFileRenumberGeneric
CREATE PROC dbo.cameraFileRenumberGeneric
/*     DATE          AUTHOR        REMARKS
       1/1/17        PPaiva        Initial creation.

              Renumbers camera files with a numeric offset.
              Generates rename code for xp_cmdshell.

              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

       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'

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'''