Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I know to query the current Compatibility Level of a database with
declare @dbname sysname
select @dbname = db_name(0)
exec sp_dbcmptlevel @dbname
But this seems to return its result via print and I have found no way to assign the value to a variable and use it to control if ... else ... constructs to execute different code depending on current setting.
You could query the system table sys.databases:
-- for SQL 2005+
declare @level tinyint;
select @level = [compatibility_level]
from sys.databases
where name = 'AdventureWorks'
Select @level
GO
-- for SQL 2000+
declare @level tinyint;
select @level = [cmptlevel]
from sysdatabases
where name = 'AdventureWorks'
Select @level
GO
