There is a MSDN forum question to change the data_compression option to change for all tables on all databases in a server.

There were many scripts available in the web. However, here I would like to present one another method usingsp_MSforeachdb and sp_MSforeachtable system procedures. This is a tricky method, with a clean code.

I have already written another script for the usage of those two procedures to get the count of tables as below:

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01


Hope, this script would help you. please download the script and give a try in your development server.

[Note: The procedures are undocumented procedures.]


Looking forward your feedback on the same.