Move All MDF - LDF Files to a new location

--Move All MDF - LDF Files to a new location
--write by Sushil Kumar Rout
CREATE PROCEDURE MoveAllDatabaseFile
@FileType CHAR (3),
@fullpath VARCHAR(500)
AS

IF @FileType not in ('MDF','LDF')
BEGIN
SELECT '@FileType must be MDF or LDF and @fullpath should be like N''C:\DatabaseFiles\'' the script was terminated' as
'ERROR'
SELECT 'example of right syntax - exec MoveAllDatabaseFile ''MDF'',N''C:\DatabaseFiles\''' as 'Example Script'
return
END
declare @dbname as varchar (100)
declare @dbfilename as varchar (100)
declare @runalter as varchar(500)
--Case LDF

IF @FileType = 'LDF'
BEGIN
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
SELECT sysdb.name AS 'DBName',masterfiles.name as 'FileName'
FROM sys.master_files masterfiles,sys.sysdatabases sysdb
WHERE masterfiles.database_id = sysdb.dbid
AND masterfiles.type_desc = 'LOG' and masterfiles.database_id > 4 --not take system DB's
END

--Case MDF
ELSE
BEGIN
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
SELECT sysdb.name as 'DBName',masterfiles.name as 'FileName'
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,@dbfilename

WHILE @@FETCH_STATUS = 0
BEGIN

SET @runalter = 'Alter database ' + @dbname + ' modify file (name = ' + @dbfilename + ' , filename = N'''+ @fullpath +
@dbfilename + '.' + @FileType + ''')'

select (@runalter)

FETCH NEXT FROM CRS_db INTO @dbname,@dbfilename
END
CLOSE CRS_db
DEALLOCATE CRS_db