Running this SQL throws error Incorrect syntax near '14': SELECT * FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , 'LIMITED');

Question

Running this SQL throws error Incorrect syntax near '14': SELECT * FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , 'LIMITED');

Salooja Johnson 1

Tried using DB_ID(), the actual DB ID, variables etc. but it never works

Anurag Sharma 17,631 Reputation points

2021-05-28T05:58:08.233+00:00

Hi @Salooja Johnson , just wanted to follow up if you were able to proceed.

2 answers

Your answer

Anurag Sharma 17,631 Reputation points

2021-05-28T05:58:08.233+00:00

Hi @Salooja Johnson , just wanted to follow up if you were able to proceed.

Answer 1

Anurag Sharma 17,631

Hi @Salooja Johnson , welcome to Microsoft Q&A forum.

Can you please try running queries in below format once and let me know if it works:

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'yourdbname'), NULL, NULL, NULL , 'LIMITED');   


SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'yourdbname'), NULL, NULL, NULL , 'DETAILED');

Just replace the database name.

If this does not work, could you please provide a screenshot of the error? Also is this an old database or something you have created recently.

Salooja Johnson 1 Reputation point

2021-06-02T04:44:21.037+00:00

Dedicated SQL Pool was created recently.
Anurag Sharma 17,631 Reputation points

2021-06-02T13:41:23.817+00:00

Thanks for providing more information. I am not sure if this function is supported in Dedicated SQL Pool(SQL Data Warehouse). However I can try to repro it once and confirm it.
Anurag Sharma 17,631 Reputation points

2021-06-03T06:04:07.337+00:00
Hi @Salooja Johnson , there are lots of system views we can use with Dedicated SQL Pool.

Example:

select * from sys.pdw_nodes_indexes

Please read through the below article that lists down the views available:

System views for dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics

Answer 2

Salooja Johnson 1

Ok thanks, if this function is not supported, then please provide details about alternate function to use.

Share via

Running this SQL throws error Incorrect syntax near '14': SELECT * FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , 'LIMITED');

2 answers

Your answer