Partition Scripts

Script to get table and its partition details SELECT fg.groupname FileGroupName,sf.name [FileLocation],sf.fileid, [filename],ds.type,ds.type_desc,object_name(sp.object_id) ObjectName,sp.index_id,sp.rows,ds.data_space_id ,au.type_desc,au.total_pages,au.used_pages,au.data_pages, sp.partition_number FROM sys.sysfiles sf INNER JOIN sys.sysfilegroups fg ON sf.groupid=fg.groupid INNER JOIN sys.data_spaces ds ON fg.groupid=ds.data_space_id INNER JOIN sys.allocation_units au ON ds.data_space_id=au.data_space_id INNER JOIN sys.partitions sp ON au.container_id=sp.hobt_id INNER JOIN sys.tables st ON sp.object_id=st.object_id –LEFT JOIN sys.partition_schemes ps ON ps.data_space_id=ds.data_space_id […]

How to get Partitioned Tables Storage Info

Query to get Partitioned Objects and Partition information USE <Database Name> GO DECLARE @Object_id int SET @Object_id=OBJECT_ID(‘Table Name’ ) SELECT OBJECT_NAME(si.object_id) TbName,si.name,si.index_id,si.data_space_id,sds.name,sp.index_id , sp.partition_id,sp.partition_number,sp.rows,sau.container_id,sau.total_pages,sau.used_pages,sau. data_pages from sys.indexes si INNER JOIN sys.data_spaces sds ON si.data_space_id=sds. data_space_id INNER JOIN sys.partitions sp ON si.index_id=sp.index_id and si.object_id=sp. object_id INNER JOIN sys.allocation_units sau ON sp.partition_id=sau. container_id where si.object_id= @Object_id GROUP […]