x

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.

more ▼

asked Jun 28, 2012 at 11:07 AM in Default

avatar image

Etienne
70 3 5 8

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jun 28, 2012 at 12:06 PM

avatar image

Usman Butt
13.9k 6 13 21

@Usman, thank you very much!!

Jun 29, 2012 at 10:39 AM Etienne

@Etienne HTH.

Jun 29, 2012 at 10:43 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

This is for db.

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

more ▼

answered Jun 28, 2012 at 11:17 AM

avatar image

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2091
x2017
x393

asked: Jun 28, 2012 at 11:07 AM

Seen: 1839 times

Last Updated: Jun 29, 2012 at 10:43 AM

Copyright 2016 Redgate Software. Privacy Policy