question

Etienne avatar image
Etienne asked

Change file names on DISK with t-sql

This is the code that I have in order to change the file names in a directory, but I must provide the full name of the file and then the new name. ALTER procedure [dbo].[sp_ReplaceFileOrDirNames] (@oldName varchar(50), @newName varchar(50)) as begin declare @winCmd varchar(400) declare @isFileThere bit declare @isDirectory bit declare @parentDirExists bit declare @fullNamewithPath varchar(250) set nocount on set @fullNamewithPath = 'C:\MyFiles\'+@oldName Create table #temp (isFileThere bit, isDirectory bit, parentDirExists bit) Insert #temp exec master..xp_fileExist @fullNamewithPath select @isFileThere = isFileThere, @isDirectory = isDirectory FROM #temp if (@isFileThere = 1) begin set @winCmd = 'rename ' + 'C:\MyFiles\'+@oldName + ' ' + @newName end else begin if (@isDirectory = 1) begin set @winCmd = 'move /Y ' + 'C:\MyFiles\'+ @oldName + ' '+'C:\MyFiles\'+@newName end end print @winCmd exec master..xp_cmdShell @winCmd drop table #temp set nocount off end I need changes to the code so that it must loop through all the files located in 'C:\MyFiles\' and just remove any apostrophes in the file names.
sql-server-2008sql-server-2005tsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
How about this SET NOCOUNT ON; CREATE TABLE #FileList ( FileID INT IDENTITY(1, 1) ,Line VARCHAR(512) ) CREATE TABLE #temp ( isFileThere BIT ,isDirectory BIT ,parentDirExists BIT ) DECLARE @Command VARCHAR(1024) , @RowCount INT , @counter INT , @FileName VARCHAR(1024) , @FileExists BIT SET @Command = 'dir C:\MyFiles\ /A-D /B' PRINT @Command INSERT #FileList EXEC master.dbo.xp_cmdshell @Command DELETE FROM #FileList WHERE Line IS NULL SELECT @RowCount = COUNT(*) FROM [#FileList] SET @counter = 1 WHILE ( @counter <= @RowCount ) BEGIN SELECT @FileName = [Line] FROM [#FileList] WHERE [FileID] = @counter SET @Command = 'C:\MyFiles\' + @FileName + '' PRINT @Command INSERT [#temp] EXEC master.dbo.xp_fileExist @Command SELECT @FileExists = [isFileThere] FROM [#temp] IF @FileExists = 1 AND CHARINDEX('''', @FileName) > 0 SET @Command = 'REN "C:\MyFiles\' + @FileName + '" "' + REPLACE(@FileName, '''', '') + '"' ELSE SET @Command = '' SET @counter = @counter + 1 PRINT @Command IF LEN(@Command) > 0 EXEC master.dbo.xp_cmdshell @Command END DROP TABLE #FileList DROP TABLE [#temp] I am sure this can be more optimized and could be more tidier. But this could lead to the solution. I also hope that you know the consequences and pitfalls of using extended procedures like xp_cmdshell etc.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Usman, thank you very much!!
0 Likes 0 ·
@Etienne HTH.
0 Likes 0 ·
ecomma avatar image
ecomma answered
This is for db. ALTER DATABASE your_db_name MODIFY FILE (NAME = logical_file_name, FILENAME = 'your-new-file-on-disk.mdf'
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

why do you want to do this: I need changes to the code so that it must loop through all the files located in 'C:MyFiles' and just remove any apostrophes in the file names.
0 Likes 0 ·
do just want to change the default location of SQL Server Data and Log files?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.