How to find out estimated saving space for Data Compression for all tables in the database?

Once, I needed to find out estimated saving space for implementation of Data Compression, for both types of compression, ROW and PAGE. For these purposes you can use system store procedure which return current size of table, estimates size for the requested compression state. Name of store procedure is sp_estimate_data_compression_savings. Let's show you, how it looks for one table:

In details:

• 'Sales' - Is the name of the database schema.

• 'SalesOrderDetail' - Is the name of the user table.

• First NULL - Is the ID of the index, if null it returns all indexes of user table as you can see below

• Second NULL - Is the partition number in the object.

• 'ROW' - Choose types of compression, it can be ROW, TYPE, NONE, COLUMNSTORE and COLUMNSTORE_ARCHIVE.

Result looks like:

As I wrote, I need to find out for all user tables estimates size for the both types compression, ROW and PAGE. Therefore, I wrote this pretty easy query that generates store procedure is sp_estimate_data_compression_savings for all user tables.

---

Run following scripts:

As you can see, there is in where clausule TABLE_TYPE = 'BASE TABLE' which means that return only user tables in result.

The second dynamic query generates store procedure for PAGE compression:

Then just copy columns from Results panel to new query and execute as you can see below:

I hope you that you save your time this simple query!