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;
