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.
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.
This is for db. ALTER DATABASE your_db_name MODIFY FILE (NAME = logical_file_name, FILENAME = 'your-new-file-on-disk.mdf'
9 People are following this question.
