| Downloads |
|---|
| 49567.zip |
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:
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 r2r@sqlmag.com. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $100.
