DB_ID() Replacement

In the BOL, it states to replace DB_ID() with a valid database name when the compatibility level is 80 or below.

The original statement is:

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

I replaced DB_ID() as follows but none worked:

DB_ID(N'pubs')
pubs

What should the syntax look like?

I tried to run the following and it worked for me:

SELECT object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent

AS frag

INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID( N'AdventureWorks'), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

|||

The BOL statement indicates the following issue:

If database is in 80 or earlier compat mode, certain newer features or syntax or keywords will not work. In this specific example, the ability to pass expressions as parameter values to TVFs is not possible if the database is below 90 compat mode. You will get a syntax error actually. So you have three alternatives to make it work:

1. Run the statement from a database that is in 90 compat mode. You can then do:

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM pubs.sys.dm_db_index_physical_stats (DB_ID('pubs'), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

2. Or specify the database id explicitly like (will work in db with any compat mode):

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM pubs.sys.dm_db_index_physical_stats (14, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- or

use pubs

go

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (14, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

3. Or use variable to specify the database id like (will work in db with any compat mode):

declare @.dbid int;

set @.dbid = db_id('pubs');

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM pubs.sys.dm_db_index_physical_stats (@.dbid, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

--or

use pubs

go

declare @.dbid int;

set @.dbid = db_id('pubs');

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (@.dbid, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;