question

sandypattu avatar image
sandypattu asked

Compression and looping of all databases

i have a doubt how to write a query which finds non compressed index and table in a database and compresses it. Also how to do it for all databases at once (like loop one database after the other)..
sql-server-2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
You'll have to write cursors to walk the objects and databases, but to get the list of objects from each is simply a matter of using the information stored in the system views: SELECT o.name, o.object_id FROM sys.objects AS o JOIN sys.partitions AS p ON p.object_id = o.object_id WHERE p.data_compression = 0 AND o.type = 'U' UNION ALL SELECT i.name, i.object_id FROM sys.indexes AS i JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.objects AS o ON o.object_id = i.object_id WHERE p.data_compression = 0 AND i.type = 2 AND o.type = 'U';
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sandypattu avatar image sandypattu commented ·
thanks a lot it helped i wrote a query which follows declare @iname sysname declare @uname sysname declare @oname sysname declare mycursor cursor for SELECT i.name,USER_NAME(o.uid),OBJECT_NAME( i.id) FROM sysindexes i inner join sysobjects o ON i.id = o.id WHERE o.type = 'U' and i.indid = 1 open mycursor Fetch NEXT FROM mycursor INTO @iname, @uname, @oname WHILE @@FETCH_STATUS = 0 BEGIN Print('ALTER INDEX ' + @iname + ' ON ' + @uname + '.' + @oname + ' REBUILD WITH ( DATA_COMPRESSION = PAGE );') Fetch NEXT FROM mycursor INTO @iname, @uname, @oname end close mycursor deallocate mycursor declare @tname sysname declare @sname sysname declare mycursor cursor for select name,schema_name(schema_id) from sys.partitions p join sys.objects o on p.object_id = o.object_id where o.type = 'U' and data_compression = 0 open mycursor Fetch NEXT FROM mycursor INTO @tname, @sname WHILE @@FETCH_STATUS = 0 BEGIN Print ('ALTER TABLE [' + @sname + '].['+ @tname+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)') Fetch NEXT FROM mycursor INTO @tname, @sname end close mycursor deallocate mycursor
0 Likes 0 ·
sandypattu avatar image sandypattu commented ·
I have other question which is-- This query will work only in one database at a time. How to make it run in databases at once (like a loop one database after the other) is there any query to that?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.