Geek asks and answers

Incorrect syntax near '(' when checking index fragmentation

I am trying to check index fragmentation in my database using SQL 2008 R2.

I am using the following code taken from http://msdn.microsoft.com/en-gb/library/ms189858(v=sql.100).aspx with a few name changes:

USE StockSystem;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.StockItems'),NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

GO

When I run it, I get an error message:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

Can anyone determine where I am going wrong?

UPDATE

It's strange if I call two functions (DB_ID and OBJECT_ID) and get the values, then substituting the values ​​in the main selection operator, everything works fine. Why can't I use two functions in SELECT, like MSDN?

UPDATE

In response to Akrem sugegstion, I also tried this, but got the same error.

USE StockSystem;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('StockSystem'), OBJECT_ID(N'dbo.StockItems'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

UPDATE

. SQL2012 . . , , SQL. .

, , SQL. . , , , .

-?

UPDATE

, . DB_ID OBJECT_ID , .

use StockSystem

declare @dbid SMALLINT
declare @objectid INT

select @dbid = DB_ID('StockSystem'), @objectid = OBJECT_ID(N'dbo.StockItems')

SELECT a.index_id,name,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@dbid,@objectid,NULL,NULL,NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
+5
2

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/20/unexpected-errors-from-table-valued-functions.aspx

, , ​​ 80 (SQL Server 2000) DB_ID, OBJECT_ID .

- , :

USE StockSystem;
GO
DECLARE
    @database_id INT = DB_ID(),
    @object_id   INT = OBJECT_ID(N'dbo.StockItems');

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@database_id ,@object_id , NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
+13

DB_ID("DataBaseName")

:

,

USE StockSystem;
GO
select  * from sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null)
0

More articles:


All Articles