How to store the result of **DBCC PDW_SHOWSPACEUSED("dbo.TableName")** in a Temporary table in Parallel Data Warehousing (APS PDW)?

Question

How to store the result of DBCC PDW_SHOWSPACEUSED("dbo.TableName") in a Temporary table in Parallel Data Warehousing (APS PDW)?

Below is my query which is not working.

create table #TempSpace(
[ROWS] bigint,
RESERVED_SPACE bigint,
DATA_SPACE bigint,
INDEX_SPACE bigint,
UNUSED_SPACE bigint,
PDW_NODE_ID bigint,
DISTRIBUTION_ID bigint
)

insert #TempSpace([ROWS],RESERVED_SPACE,DATA_SPACE,INDEX_SPACE,UNUSED_SPACE,PDW_NODE_ID,DISTRIBUTION_ID) EXEC('DBCC PDW_SHOWSPACEUSED("dbo.TableName")')

Answer 1

You often have to add WITH TABLERESULTS to DBCC commands to get a format you can work with.

However, I can't say whether it works in particular case. I don't use APS myself.

Answer 2

Hi anonymous user,

Sorry I am not familiar with Parallel Data Warehouse. Not sure if this statement is supported.

Azure SQL Data Warehouse does not support INSERT ... EXEC
https://learn.microsoft.com/en-gb/azure/sql-data-warehouse/sql-data-warehouse-develop-stored-procedures#insertexecute

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Sorry, I am late to this, but the only way to do this with APS (PDW) or Synapse (ADW) is using Powershell (let me know if you need code for this, but it is pretty trivial) or .NET, as there is no support for WITH RESULTSETS when using DBCC PDW_SHOWSPACEUSED

And no there is also no support for INSERT ... EXEC on either APS or Synapse. This is from the INSERT (Transact-SQL)
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse

INSERT INTO { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ( column_name [ ,...n ] ) ]
{
VALUES ( { NULL | expression } )
| SELECT <select_criteria>
}
[ OPTION ( <query_option> [ ,...n ] ) ]
[;]

Anthony