sys.dm_db_index_physical_stats

Question

sys.dm_db_index_physical_stats

sarahmp

SSC Veteran

Points: 206
More actions
January 24, 2019 at 6:19 am

#379121

Hello,
I'm trying to run the following query in Microsoft SQL Server Management Studio 17. But I keep getting an error. Can anybody help me figuring out what I'm doing wrong here. Thanks in advance.
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, 'detailed');
I also tried the following query's, but none seem to work.
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, detailed);
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, NULL);

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

sarahmp - Thursday, January 24, 2019 6:19 AM
Hello,
I'm trying to run the following query in Microsoft SQL Server Management Studio 17. But I keep getting an error. Can anybody help me figuring out what I'm doing wrong here. Thanks in advance.
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, 'detailed');
I also tried the following query's, but none seem to work.
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, detailed);
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, NULL);

What error are you getting? The first one works for me.

Thanks

sarahmp SSC Veteran Points: 206 More actions · Answer 2

Thanks for responding. The error I'm getting is:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax

It also says: An insufficient number of arguments were supplied for the procedure or function sys.dm_db_index_physical_stats.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

This what I'm running (Database Name and Object Name have been changed):

SELECT * FROM sys.dm_db_index_physical_stats(db_id('dba_database'), object_id('DDL_EVENTS'), NULL, NULL, 'detailed');
Are you running it on a SQL Server 2017 database?

Thanks

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

sarahmp - Thursday, January 24, 2019 6:19 AM
Hello,
I'm trying to run the following query in Microsoft SQL Server Management Studio 17. But I keep getting an error. Can anybody help me figuring out what I'm doing wrong here. Thanks in advance.
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, 'detailed');
I also tried the following query's, but none seem to work.
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, detailed);
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(*name database*), object_id(*name table*), NULL, NULL, NULL);

If you're using that exact same code and substituting the names, then the problem is likely the "*" that you're using instead of single quotes.

Word of advice for future posts... post the actual error that you're getting. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sarahmp SSC Veteran Points: 206 More actions · Answer 5

Thank you both. The problem was indeed in the quotations.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

sarahmp - Sunday, January 27, 2019 6:32 AM
Thank you both. The problem was indeed in the quotations.

Thank you for the feedback and the confirmation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)