Use below script to check database compression on instance? Create Table #Compression_Details(TableName VARCHAR(500),IsCompressed INT, Compressed_Desc VARCHAR(1000),DBNAME VARCHAR(500)) DECLARE @SQLString VARCHAR(MAX) DECLARE @DBIn VARCHAR(500) DECLARE Check_Compression CURSOR FOR SELECT NAME FROM sys.databases WHERE database_id >4 OPEN Check_Compression FETCH NEXT FROM Check_Compression INTO @DBIn WHILE @@FETCH_STATUS =0 BEGIN SET @DBIn = '['+ @DBIn+']' SET @SQLString = 'USE ' + @DBIn + '; INSERT INTO #Compression_Details(TableName,IsCompressed,Compressed_Desc,DBNAME) Select T.name,P.data_compression,P.data_compression_desc,DB_NAME() FROM sys.partitions P JOIN sys.tables T ON (P.object_id = T.object_id) JOIN sys.schemas S ON (T.schema_id = S.schema_id) WHERE P.data_compression <>0' EXEC(@SQLString) PRINT (@DBIn) FETCH NEXT FROM Check_Compression INTO @DBIn END CLOSE Check_Compression DEALLOCATE Check_Compression SELECT * FROM #Compression_Details DROP TABLE #Compression_Details
You don't actually perform compression at the database level, it's always done at the table level. I also probably wouldn't set a policy as regards compression level. Depending upon the datatypes used, the variable values of that data and your performance requirements you may not want to use compression at all. You should really be establishing that need on a per table basis and checking it against your workload and server performance (ensuring that you start off with a good baseline). I know that this does completely cover your question, I'm just a little concerned that you are headed down a road that you should not be going down.
I totally agree with @SirSQL. You should re-consider such policy. But I guess this can be done by choosing the **table** facet. Following what could be the condition script ExecuteSql('Numeric', 'select COUNT(*) FROM sys.partitions P JOIN sys.tables T ON (P.object_id = T.object_id) JOIN sys.schemas S ON (T.schema_id = S.schema_id) WHERE T.name = @@ObjectName AND S.name = @@SchemaName AND P.data_compression = 0 AND P.index_id IN (0,1)') and should be evaluated against value "0". Please note that I have added the filter AND P.index_id IN (0,1) to omit the secondary indexes. If you want the indexes to be evaluated as well, then remove that filter. By default, the policy would be for every table/ every database for your instance.