you can use this improved script.

--This script create a store procedure that Move All MDF - LDF Files

--Wrote by Avi Grinberg

create procedure MoveAllDatabaseFile

@FileType char (3),@fullpath varchar(100)

as

if @FileType not in ('MDF','LDF','mdf','ldf')

begin

print '@FileType must be MDF or LDF (or lower case) and @fullpath should be like N''C:\DatabaseFiles\'' the script was terminated!.'

print 'example of right syntax - exec MoveAllDatabaseFile ''MDF'',N''C:\DatabaseFiles\'''

return

end

if @fullpath not like '%\'

begin

print 'The @fullpath must end with a ''\'' ,this script is terminated!. '

return

end

declare @dbname as varchar (100)

declare @LogicalFileName as varchar (100)

declare @PhysicalFileName as varchar (100)

declare @runalter as varchar(500)

--Case LDF

if @FileType = 'LDF' or @FileType = 'ldf'

begin

DECLARE CRS_db CURSOR LOCAL FAST_FORWARD

FOR

select sysdb.name as 'DBName',masterfiles.name as 'LogicalFileName',substring(right(physical_name,charindex('\',reverse(physical_name))),2,100) as 'PhysicalFileName'

from sys.master_files masterfiles,sys.sysdatabases sysdb

where masterfiles.database_id = sysdb.dbid

and masterfiles.type_desc = 'LOG' and masterfiles.database_id > 4

end

--Case MDF

else

begin

DECLARE CRS_db CURSOR LOCAL FAST_FORWARD

FOR

select sysdb.name as 'DBName',masterfiles.name as 'LogicalFileName',substring(right(physical_name,charindex('\',reverse(physical_name))),2,100) as 'PhysicalFileName'

from sys.master_files masterfiles,sys.sysdatabases sysdb

where masterfiles.database_id = sysdb.dbid

and masterfiles.type_desc = 'ROWS' and masterfiles.database_id > 4 --not take system DB's

end

--Start execute MDF or LDF

OPEN CRS_db

FETCH NEXT FROM CRS_db INTO @dbname,@LogicalFileName,@PhysicalFileName

WHILE @@FETCH_STATUS = 0

BEGIN

Set @runalter = 'Alter database ' + @dbname + ' modify file (name = ' + @LogicalFileName + ' , filename = N'''+ @fullpath + @PhysicalFileName + ''')'

select (@runalter)

FETCH NEXT FROM CRS_db INTO @dbname,@LogicalFileName,@PhysicalFileName

END

CLOSE CRS_db

DEALLOCATE CRS_db