Rename Files or Directories On the Fly in T-SQL Scripts

With the sp_ReplaceFileOrDirNames procedure, you can rename a file or directory in the SQL Server file system from within the SQL Server environment. I wrote this procedure for use in T-SQL scripts that perform database tasks but need to change file or directory names during their execution. By having a T-SQL stored procedure rename the file or directory, you avoid having to use both a batch script and a T-SQL script for such tasks.

The sp_ReplaceFileOrDirNames procedure uses the xp_fileExist extended stored procedure to distinguish between files and directories and to check whether the specified object does indeed exist in the file system. When the object is a file, the file bit is on and a Rename command is constructed to rename the file. When the object is a directory, the directory bit is on and a Move command is constructed to rename the directory. Rename and Move are Windows shell commands, so the sp_ReplaceFileOrDirNames procedure uses the xp_cmdShell extended stored procedure to execute them.

Listing 1 shows the code for the sp_ReplaceFileOrDirNames procedure. Here’s an example of how to use this procedure:

-- Rename a directory called sql to mssql in C:\appl.
exec sp_ReplaceFileOrDirNames @pathToObject= 'c:\appl', 
                              @oldName='sql',
                              @newName='mssql'
-- Rename a file called my Text.txt to test.txt in C:\appl\mssql.
exec sp_ReplaceFileOrDirNames @pathToObject= 'c:\appl\mssql',
                              @oldName='myText.txt',
                              @newName='test.Txt'

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (500 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $100.