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'
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 firstname.lastname@example.org. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $100.