problem with sys.dm_db_index_physical_stats | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

problem with sys.dm_db_index_physical_stats

hi,
i am using SQL Server 2005. i have a database DBOneshelp, in that Database there is a table – tblSearch. now i want to see the fragmentation status of that table so execute the following Query:
SELECT PK_SalesId, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N’DBOneshelp’), OBJECT_ID(N’tblSearch’), NULL, NULL , ‘Detailed’)
go
but there is a syntax error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘(‘.
can any one tell me what is the error i have done in the above sql statement
thanks in advance
regards,
suvresh

http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx
http://msdn2.microsoft.com/en-us/library/ms188917.aspx fyi

try the following:
SELECT PK_SalesId, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(‘DBOneshelp’), OBJECT_ID(‘tblSearch’), NULL, NULL , ‘Detailed’)

still giving same error! not working

try select *

remove the single quotes from around ‘Detailed’

Just take your database out of SQL 80 compatibility mode and put it in to 90.

thanks comeaugilles
it is now really working, thankx again.
regards,
suvresh

You can use the following with the compatible level 80
Declare @db_id smallint
Declare @tab_id int
set @db_id=db_id(‘DBOneshelp’)
set @tab_id=object_id( ‘tblSearch’)
SELECT PK_SalesId, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(@db_id,@tab_id, NULL, NULL , ‘Detailed’)
go

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |