how to write a generic script without explicitly writing the database name when it is a mandatory parameter

Question

I need to run a script like

EXEC sp_dbcmptlevel AdventureWorks, 100;
GO

Now I'd like to have a generic version that uses the database I am connected to, is it possible in T-SQL?

Something like this, but this doesn't work:

EXEC sp_dbcmptlevel DBNAME(), 100;
GO

It doesn't work of course because a string is returned, I'd like to have the database referece returned... Is it possible?

Note Of course I can do it from a client application and using a parameter, like (Delphi example)

EXEC sp_dbcmptlevel :CurrentDatabase, 100;
GO

and replace it at runtime, but I'd like to have T-SQL only code.

Answer 1

You can use the EXEC approach.

declare @x nvarchar(2000)
set @x = 'alter database [' + DB_NAME() + '] set compatibility_level = 100;'
exec (@x)

BOL 2008 declares that sp_dbcmptlevel is deprecated, and to use ALTER DATABASE instead. I don't know whether 2005 supports this or not.

Answer 2

You can try

DECLARE @dbname AS nvarchar(500)
set @dbname = db_name()
exec sp_dbcmptlevel @dbname, 100;

Your server must of'course support the compatibility level you try to set ..

You should also have a look at this http://msdn.microsoft.com/en-us/library/ms178653.aspx