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
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