So you need to add page level compression to every table in your SQL Server database? I'm not going to judge you. I'm not going to tell you it's a good idea or a bad idea. I needed to do it in my DEV environment and maybe you need to do it in your production SQL Server environment. I can help you. Listen. I'm not particularly proud of this script. It's not complex or difficult. It didn't take hours of finally honed SQL-smithing to make it work. It just solved a problem. Many people consider me the Winston Wolf of the SQL world. Well, actually I'm the only one who considers myself the Winston Wolf of the SQL world but whatever.
Who is Winston Wolf you ask? Pulp Fiction my friend.
The Wolf: You're... Jimmie, right? This is your house?
Jimmie: Sure is.
The Wolf: I'm Winston Wolfe. I solve problems.
Jimmie: Good, we got one.
The Wolf: So I heard. May I come in?
Jimmie: Uh, yeah, please do.
So, just run the little diddy below and it will produce a script that will add page compression to each table. Boom. You're golden.
SELECT 'ALTER TABLE [' + table_schema + '].[' + table_name + '] REBUILD WITH (DATA_COMPRESSION = PAGE);' FROM INFORMATION_SCHEMA.tables WHERE INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE'