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

Etienne gravatar image

Etienne
70 3 3 5

(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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

@Usman, thank you very much!!
Jun 29, 2012 at 10:39 AM Etienne
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

ecomma gravatar image

ecomma
460 16 19 22

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1947
x1839
x288

asked: Jun 28, 2012 at 11:07 AM

Seen: 1333 times

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