CODEWITHSUNDEEP
sql-serversql-server-2005
marioosh
marioosh

Reputation: 28556

ALTER current database without using its name

I need to run update script on current database (ALTER DATABASE...), but can't use implicit its name. Is possible to use some function to get current db name and use inside ALTER DATABASE (Sql Server 2005 and above) ? I tried use db_name(), but doesn't work.

select db_name(); works

ALTER DATABASE db_name() ... doesn't work

Upvotes: 29

Views: 13888

Answers (3)

Russ B
Russ B

Reputation: 880

Actually something more like this is probably a little better if you're altering the current database:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 90

Upvotes: 72

Rich S
Rich S

Reputation: 3453

You need to use something like

declare @dbname varchar(100)
set @dbname=quotename(db_name())
exec('alter database '+@dbname+' ...');

or.. even simpler

set @sql='alter database '+quotename(db_name())+' ...';
exec(@sql)

Upvotes: 22

Raj
Raj

Reputation: 10843

Try this

DECLARE @DBName sysname;
SET @DBName = (SELECT db_name());
DECLARE @SQL varchar(1000);
SET @SQL = 'ALTER DATABASE '+@DBName+' .......'

Raj

Upvotes: 0

Related Questions