0

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

1 Answer 1

3

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.