Space estimates for each table if compression is removed

Question

I am trying to gather space estimates if compression is removed from tables in a database. I started with a temp table and generated statements to execute sp_estimate_data_compression_savings as shown below:

insert into #TEMP2 
exec sp_estimate_data_compression_savings 'dbo', 'table_name',NULL,NULL,'none'

That command works fine, and inserts the size estimate into a temp table. I am trying to wrap this into a sp_MSforeachtable statement:

declare @command varchar(1000)
select @command = 'insert into #temp2 exec sp_estimate_data_compression_savings 
    ''dbo'', ''?'', NULL, NULL, ''none'''
exec sp_MSforeachtable @command

This generates all table does not exist or you do not have permissions errors for every table in the database:

Msg 1088, Level 15, State 1, Procedure sp_estimate_data_compression_savings
Cannot find the object "[dbo].[table_name]" because it does not exist
or you do not have permissions.

I assumed the database context was being changed somewhere in the sp_MSforeachtable command so I tried adding USE DBNAME to the command as shown below, but that didn't seem to have any effect, and it generated the same errors.

declare @command varchar(1000)
select @command = 'USE DBNAME 
    insert into #pattemp2 
    exec sp_estimate_data_compression_savings 
        ''dbo'',''?'',NULL,NULL,''none'''
exec sp_MSforeachtable @command

Answer 1

Because that will run

exec sp_estimate_data_compression_savings 'dbo','[dbo].[table_name]',NULL,NULL,'none'

instead of

exec sp_estimate_data_compression_savings 'dbo','table_name',NULL,NULL,'none'

Why? Because sp_MSforeachtable is undocumented and you shouldn't be using it. Write a cursor, or find a replacement on the internet. eg

set nocount on
drop table if exists #results
create table #results
(
  object_name sysname,
  schema_name sysname,
  index_id int,
  partition_number int,
  [partition_number size_with_current_compression_setting(KB)] bigint,
  [size_with_requested_compression_setting(KB)] bigint,
  [sample_size_with_current_compression_setting(KB)] bigint,
  [sample_size_with_requested_compression_setting(KB)] bigint
)
declare @schema sysname
declare @table sysname

declare c cursor local static for 
    select schema_name(schema_id), name
    from sys.tables
    where type_desc = 'USER_TABLE'
open c

fetch next from c into @schema, @table 
while @@FETCH_STATUS = 0
begin
   insert into #results
   exec sp_estimate_data_compression_savings @schema, @table,NULL,NULL,'none'

   fetch next from c into @schema, @table 
end
close c
deallocate c
select * from #results