Change file names on DISK with t-sql

Question

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.

Answer 1

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 &lt;= @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) &gt; 0 
             SET @Command = 'REN &quot;C:\MyFiles\' + @FileName + '&quot; &quot;'
                 + REPLACE(@FileName, '''', '') + '&quot;'
         ELSE 
             SET @Command = ''
  
         SET @counter = @counter + 1
  
         PRINT @Command
  
         IF LEN(@Command) &gt; 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.

Answer 2

0

This is for db.

ALTER DATABASE your_db_name MODIFY FILE (NAME = logical_file_name, FILENAME = 'your-new-file-on-disk.mdf'

answered Jun 28, 2012 at 11:17 AM

ecomma
470 ● 18 ● 22 ● 26

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.

Jun 28, 2012 at 11:25 AM ecomma

do just want to change the default location of SQL Server Data and Log files?

Jun 28, 2012 at 11:26 AM ecomma

(comments are locked)

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Change file names on DISK with t-sql

Follow this question

Related Questions