Change Db name in script automatically
Gavin
Posts: 4 New member
How do I run this against multiple DBs, where "TestDb" has to change dynamically to match the selected DB/s name?
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
Tagged:
Best Answer
-
Alex B
Posts: 1,158 Diamond 4
Hi @Gavin
Hm, righto, looks like SQL doesn't like using a variable for a database name (or isn't expecting one).
The answer in this SO article seems to cover almost this specific example (https://stackoverflow.com/questions/727788/how-to-use-a-variable-for-the-database-name-in-t-sql/727815) and I've adapted some of the suggestions from the comments as well.<div>DECLARE @DBNAME SYSNAME;</div><div>SET @DBNAME = DB_NAME();</div><div><br></div><div>DECLARE @RECOVERY_TEMPLATE VARCHAR(MAX); --could possibly use 176 as max dbname length is 128</div><div>SET @RECOVERY_TEMPLATE='ALTER DATABASE {DBNAME} SET RECOVERY SIMPLE WITH NO_WAIT';</div><div><br></div><div>DECLARE @SQL_SCRIPT VARCHAR(MAX);--could possibly use 176 as max dbname length is 128</div><div>SET @SQL_SCRIPT = REPLACE(@RECOVERY_TEMPLATE, '{DBNAME}', QUOTENAME(@DBNAME));</div><div><br></div><div>EXECUTE (@SQL_SCRIPT)</div>Hope that helps!
Kind regards,
Alex
Answers
You should just be able to use the built in function DB_NAME()
I've done a small test and this returns the database name correctly:
Kind regards,
Alex
Have you visited our Help Center?
"Incorrect syntax near 'DB_NAME'."