About Me

My photo
Colorado
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.

Monday, April 24, 2017

Renumber or rename files in a folder

SCENARIO
You take photos on your camera, they are named something similar to:
    DSC_0001.nef
    DSC_0002.nef
    ...
    DSC_0421.nef

During your photoshoot, your memory card becomes full so you swap cards.  After your photoshoot your new card has photos with these names:
    DSC_0001.nef
    DSC_0002.nef
    ...
    DSC_0141.nef

You want to rename the second batch of files to be renumbered, starting from 0422 ending at 0562.

I'm not sure what other photographers do, but since I also wear an SQL hat, I simply created this proc:


USE Admin
IF OBJECT_ID('dbo.cameraFileRenumber') Is Not Null
       DROP PROC dbo.cameraFileRenumber
go
CREATE PROC dbo.cameraFileRenumber
       @Path varchar(500),
       @Base varchar(30),
       @NumDigits int,
       @OffsetAdd int,
       @Debug bit = 1
AS
/*     DATE          AUTHOR        REMARKS
       1/1/17        PPaiva        Initial creation.

       DESCRIPTION
              Renumbers camera files with a numeric offset.

       SCENARIO
              You do a photoshoot with a memory card.  Photos are numbered as such:
                     DSC_0001.nef    to   DSC_0421.nef  (421 files)
              Your memory card fills up.  You change memory cards, the new photos are now numbered:
                     DSC_0001.nef    to   DSC_0141.nef  (141 files)
              You have a problem - you can't put all the files into the same folder because they
                     start counting from the same base number.  Therefore you have overlapping file names.
              Solution:  Rename the second set of files to start counting at 0422. 
              Desired end result:  DSC_0001.nef to     DSC_0562.nef  (562 files)


       USAGE
              -- Debug
              cameraFileRenumber
                     'K:\Images\Nikon\2017\2017-12-17_Elizabeth\batch2',
                     'DSC_', 4, 421
                     , 0

              -- Execute
              cameraFileRenumber 'K:\Images\Nikon\2017\2017-12-17_Elizabeth\batch2', 'DSC_', 422

       DEBUG
              xp_fixeddrives

              xp_cmdshell '

*/
SET NOCOUNT ON

DECLARE @s varchar(1000)

SET @s = 'dir /b "' + @Path + '"'

CREATE TABLE #t (
       ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
       Filename varchar(30),
       FilenameNoExt varchar(30),
       Extension varchar(10),
       sNumWithoutBase varchar(10),
       NumWithoutBase int,
       NewNumWithoutBase int,
       NewFilename varchar(30),
       RenameCommand varchar(1100)
       )

SET @Path = dbo.udfAddBackslashIfNec(@Path)


-- Get filenames into table
INSERT INTO #t (Filename)
       Exec xp_cmdshell @s

DELETE
FROM #t
WHERE Filename Is Null

IF @Debug = 1
       SELECT  Filename,
                     CHARINDEX('.', Filename) Pos,
                     Left(Filename, CHARINDEX('.', Filename) - 1) FilenameNoExt,
                     Substring(Filename, CHARINDEX('.', Filename) + 1, 100 ) ExtOnly
       FROM #t



UPDATE #t
SET FilenameNoExt = Left(Filename, CHARINDEX('.', Filename) - 1),
       Extension = Substring(Filename, CHARINDEX('.', Filename) + 1, 100 )

UPDATE #t
SET    sNumWithoutBase = Replace(FilenameNoExt, @Base, '')

UPDATE #t
SET NumWithoutBase = convert(int, sNumWithoutBase)
      
UPDATE #t
SET NewNumWithoutBase = NumWithoutBase + @OffsetAdd

UPDATE #t
SET NewFilename = @Base + dbo.udfPadLeft(NewNumWithoutBase, @NumDigits, '0') + '.' + Extension

UPDATE #t
SET RenameCommand = 'rename "' + @Path + Filename + '" "' + NewFilename + '"'

IF @Debug = 1
       SELECT  *
       FROM #t
       ORDER BY ID


DECLARE @MaxID int,
              @ID int,
              @Cmd varchar(1100)

SELECT @MaxID = Max(ID),
              @ID = 0
FROM #t


WHILE @ID < @MaxID
       BEGIN
              -- Get next ID
              SELECT @ID = Min(ID)
              FROM #t
              WHERE ID > @ID

              -- Get other attribs
              SELECT @Cmd = RenameCommand
              FROM #t
              WHERE ID = @ID

              IF @Debug = 0
                     Exec xp_cmdshell @cmd

              Print @cmd

       END




Sunday, March 19, 2017

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      


Monday, February 27, 2017

Configure Disk Allocation Unit Size to Maximize IOPS

When configuring a disk volume for use with SQL Server, it is best to configure the Allocation Unit Size appropriately to maximize IOPS (I/O operations/second).

o    Set to NTFS 64 KB for volumes containing mostly SQL data and log files.
o    Do not use default of 4096 bytes.
o    This is because SQL stores data in 8 K pages, and there are 8 pages/extent. Therefore 64 KB/extent. This will reduce the number of IOPS.


After a new volume has been added, go to Computer Management, then Disk Management.