Database name in .sql script

Question

I need to execute the following statement

ALTER DATABASE DatabaseName ...

in my .sql script. But I want to keep my script neutral to the specific database. So I want to make ALTER DATABASE work on the current database. I hoped DatabaseName is optional parameter, but according to documentation, it is not.

faester · Accepted Answer · 2011-07-05 04:35:11Z

3

Database name is required and you cannot even put it in a parameter.

But you can use 'exec' instead

    declare @stmt varchar(max)
    set @stmt = 'alter database....'
    exec (@stmt)

It is not very elegant, but i believe it is the only way to do it

edited Jul 5, 2011 at 4:35

answered Jul 5, 2011 at 4:24

faester

15.1k5 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Fellmeister Over a year ago

Got a slight tyop in there. <sic> But the approach is perfectly sound.

SiberianGuy Over a year ago

You said I can't put database name in parameter... Why?

faester Over a year ago

I don't know the motivation for the design, but 'alter database @name...' is simply not valid.

SiberianGuy Over a year ago

But there is another kind of parameters: msdn.microsoft.com/en-us/library/ms188714.aspx

faester Over a year ago

Cool, that is of course useful. But strictly speaking I guess it wouldn't be a SQL parameter but a scripting variable. It's definately useful though!

Pero P. · Accepted Answer · 2011-07-05 05:17:26Z

1

db_name() returns the current database, which you can then stuff into an EXEC statement

DECLARE @dbname sysname = db_name()

EXEC('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

edited Jul 5, 2011 at 5:17

answered Jul 5, 2011 at 4:35

Pero P.

27.1k9 gold badges65 silver badges87 bronze badges

Comments

Tudor Constantin · Accepted Answer · 2011-07-05 04:27:41Z

0

Well,

This is one of the reasons why so many ORMs appeared for almost all the languages possible. To be sure you offer as much DB independence as possible, I recommend you to use one of them.

answered Jul 5, 2011 at 4:27

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

2 Comments

Pero P. Over a year ago

Code-first schema generation aside, I don't believe issuing DDL statements is really a common use case for ORMs.

Tudor Constantin Over a year ago

@cpedros - indeed it might not be, but those are the tools that might help the OP to reach DB structure creation independence in the fastest possible manner

Mike Venzke · Accepted Answer · 2011-07-05 04:28:16Z

0

Can't you just use a scripting language to wrap this sql call? It would be much easier and cleaner.

You can accomplish this through a series of convoluted SQL commands. If you're using MySQL, an example of creating your own SQL statement using variables and CONCAT is shown here:

http://blog.mclaughlinsoftware.com/2011/01/19/prepared-statement-failure/

As it says in the article, for ALTER statements, you have to CONCAT the database name into the statement variable. You can then use other SQL commands to get the database name you want, like DATABASE().

answered Jul 5, 2011 at 4:28

Mike Venzke

7846 silver badges10 bronze badges

Comments

shelhamer · Accepted Answer · 2011-07-05 05:04:34Z

0

EDIT: I retract my answer, the answers using EXEC are the right idea in this case.

Haven't need this in a while, but I believe you can use SELECT db_name() as a subquery, like so:

ALTER DATABASE (SELECT db_name()) ...

To run the relevant command on the current database, if that works for your use case.

edited Jul 5, 2011 at 5:04

answered Jul 5, 2011 at 4:27

shelhamer

31.4k2 gold badges34 silver badges33 bronze badges

Collectives™ on Stack Overflow

Database name in .sql script

5 Answers 5

5 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related