SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX) = '' DECLARE @EXEC NVARCHAR(MAX) = '' DECLARE @RUN BIT = 0 DECLARE @newdatalocation NVARCHAR(256) = ‘L:\temptempdb' DECLARE @newLogLocation NVARCHAR(256) = ‘M:\tempdb_log’ DECLARE @fileName NVARCHAR(64) DECLARE @fileLocation NVARCHAR(256)

DECLARE cur CURSOR FOR SELECT f.NAME ,f.filename FROM sys.sysfiles f

OPEN cur

FETCH NEXT FROM cur INTO @fileName ,@fileLocation

PRINT @fileName PRINT @fileLocation

WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = ‘ALTER DATABASE ' + DB_NAME() + ' modify FILE ( NAME = ' + f.NAME + CHAR(10) + ‘,filename = ' + '’’’ + CASE WHEN RIGHT(f.filename, 3) = ‘ldf’ THEN @newLogLocation ELSE @newdatalocation END + '' + f.NAME + '' + RIGHT(f.filename, 4) + '''' + ‘)’ + CHAR(10) FROM sys.sysfiles f WHERE f.NAME = @fileName

PRINT @SQL

SELECT @EXEC = @EXEC + @SQL

FETCH NEXT FROM cur INTO @fileName ,@fileLocation END

CLOSE cur;

DEALLOCATE cur;

IF @RUN = 1 BEGIN EXEC @EXEC END

Happy Scripting!