May 11, 2016 at 5:19 pm
Hello All...need your help ,
I have 2 db's(@DName1 and @DName2). Need to change the logical names of @DName1 to @DName2 names.
Executing --Stmt_1 gives --Stmt_2 and then executing --Stmt_2 will change the logical names.
But, need to get everything at single shot. Need help in getting dynamic sql @SQL so that everything can be done at one time.
DECLARE @DName1 sysname
SET @DName1 = 'testzz'
DECLARE @DName2 sysname
SET @DName2 = 'test_new'
--Stmt_1
SELECT 'ALTER DATABASE '+@DName1+ ' MODIFY FILE (NAME=N'''+m.[name]+''', NEWNAME=N'''+REPLACE(m.[name],@DName1,@DName2)+''')'
FROM sys.master_files m
JOIN sys.databases d ON m.database_id = d.database_id
WHERE d.[name] = 'testzz'
--Stmt_2
ALTER DATABASE testzz MODIFY FILE (NAME=N'testzz', NEWNAME=N'test_new')
ALTER DATABASE testzz MODIFY FILE (NAME=N'testzz_log', NEWNAME=N'test_new_log')
DECLARE @SQL NVARCHAR(2000)
SET @SQL = ??? ----Stmt_1(Stmt_2)
EXECUTE sp_executesql @SQL
May 11, 2016 at 9:59 pm
etirem (5/11/2016)
Hello All...need your help ,I have 2 db's(@DName1 and @DName2). Need to change the logical names of @DName1 to @DName2 names.
Executing --Stmt_1 gives --Stmt_2 and then executing --Stmt_2 will change the logical names.
But, need to get everything at single shot. Need help in getting dynamic sql @SQL so that everything can be done at one time.
DECLARE @DName1 sysname
SET @DName1 = 'testzz'
DECLARE @DName2 sysname
SET @DName2 = 'test_new'
--Stmt_1
SELECT 'ALTER DATABASE '+@DName1+ ' MODIFY FILE (NAME=N'''+m.[name]+''', NEWNAME=N'''+REPLACE(m.[name],@DName1,@DName2)+''')'
FROM sys.master_files m
JOIN sys.databases d ON m.database_id = d.database_id
WHERE d.[name] = 'testzz'
--Stmt_2
ALTER DATABASE testzz MODIFY FILE (NAME=N'testzz', NEWNAME=N'test_new')
ALTER DATABASE testzz MODIFY FILE (NAME=N'testzz_log', NEWNAME=N'test_new_log')
DECLARE @SQL NVARCHAR(2000)
SET @SQL = ??? ----Stmt_1(Stmt_2)
EXECUTE sp_executesql @SQL
Quick suggestion
😎
DECLARE @DName1 sysname = N'TEEST'
DECLARE @DName2 sysname = N'test_new'
DECLARE @RENAME_FILES NVARCHAR(MAX) = (
SELECT 'ALTER DATABASE ' + QUOTENAME(@DName1) + ' MODIFY FILE (NAME=N'''+m.[name]+''', NEWNAME=N'''+REPLACE(m.[name],@DName1,@DName2)+''');
'
FROM sys.master_files m
JOIN sys.databases d ON m.database_id = d.database_id
WHERE d.[name] = @DName1
FOR XML PATH(''), TYPE
).value('(./text())[1]','NVARCHAR(MAX)');
SELECT @RENAME_FILES;
--EXEC SP_EXECUTESQL @RENAME_FILES;
May 11, 2016 at 10:18 pm
thank you... that helped!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply