List all Objects and Indexes per Filegroup / Partition

— List all Objects and Indexes — per Filegroup / Partition and Allocation Type — including the allocated data size SELECT DS.name AS DataSpaceName ,AU.type_desc AS AllocationDesc ,AU.total_pages / 128 AS TotalSizeMB ,AU.used_pages / 128 AS UsedSizeMB ,AU.data_pages / 128 AS DataSizeMB ,SCH.name AS SchemaName ,OBJ.type_desc AS ObjectType ,OBJ.name AS ObjectName ,IDX.type_desc AS IndexType ,IDX.name […]

Move Tables To Filegroup

1. Move all tables, indexes and heaps, from all schemas into the filegroup named SECONDARY: EXEC dbo.sp_MoveTablesToFileGroup @SchemaFilter = ‘%’, — chooses schemas using the LIKE operator @TableFilter = ‘%’, — chooses tables using the LIKE operator @DataFileGroup = ‘SECONDARY’, — The name of the filegroup to move index and in-row data to. @ClusteredIndexes = […]

Gradually Deleting Data in SQL Server

If you have a situation where you have a very large table in SQL Server, where you need to periodically delete tens of millions of rows of data, there are several ways to do it. If you have a maintenance window (or your database is not required to be available 24 x 7 x 365), […]