This scripts returns all information on tables & indexes in your database. Partitioned tables/indexes are broken down by each partition. You get row counts, filegroup, compression and space used and index usage.

I use this script on databases I’m new to and I want to get I idea of large tables and where data is being located. I often copy the output into Excel and generate pivot tables grouping up by filegroup or table to get an overview.

You can run for a single table by uncommenting the WHERE clause.

SELECT O.object_id, O.name TableName, ISNULL(I.[name],'HEAP') IndexName, 
    i.type_desc [IndexType], ISNULL(SDS.name,NPSDS.[name]) FileGroup,
    PS.row_count [RowCount], CAST(PS.used_page_count * 8 AS money)/1024 SpaceUsed_MB, 
    CAST(PS.reserved_page_count * 8 AS money)/1024 ReservedSpace_MB,
    ISNULL(PAS.name,'Not Partitioned') [PartitionName], ISNULL(PF.name,'Not Partitioned') [PartitionFunction], 
    CASE WHEN PAS.name IS NOT NULL AND RV.[value] IS NULL THEN 'OutOfBounds' ELSE RV.[value] END RangeValue,
    PF.boundary_value_on_right RightBound, P.data_compression_desc [Compression],
    S.User_Seeks, S.User_Scans, S.User_Lookups, S.User_Updates,
    (SELECT SC.name+','
            FROM sys.index_columns IC 
            JOIN sys.columns SC ON SC.[object_id] = IC.[object_id] AND SC.column_id = IC.column_id
            WHERE I.index_id = IC.index_id AND IC.[object_id] = O.[object_id] AND IC.is_included_column = 0
            FOR XML PATH('')
            ) IndexColumns,
    (SELECT SC.name+','
            FROM sys.index_columns IC 
            JOIN sys.columns SC ON SC.[object_id] = IC.[object_id] AND SC.column_id = IC.column_id
            WHERE I.index_id = IC.index_id AND IC.[object_id] = O.[object_id] AND IC.is_included_column = 1
            FOR XML PATH('')
            ) IndexColumnsIncluded
FROM sys.dm_db_partition_stats PS
    JOIN sys.objects O ON O.object_id = PS.object_id AND O.type = 'U'
    LEFT JOIN sys.indexes I ON I.object_id = O.object_id AND PS.index_id = I.index_id
    LEFT JOIN sys.dm_db_index_usage_stats S ON S.[object_id] = I.[object_id] AND s.index_id = i.index_id AND S.database_id = DB_ID()
    LEFT JOIN sys.partition_schemes PAS ON I.Data_Space_ID = PAS.data_space_id
    LEFT JOIN sys.partition_functions PF ON PF.function_id = PAS.Function_ID
    LEFT JOIN sys.partition_parameters PP ON PP.function_id = PF.function_id
    LEFT JOIN sys.partitions P    ON P.object_id = O.object_id and P.index_id = i.index_id  and p.partition_id = PS.partition_id
    LEFT JOIN sys.partition_range_values RV ON RV.function_id = PF.function_id AND RV.boundary_id = CASE WHEN ISNULL(PF.boundary_value_on_right,0) = 1 THEN p.partition_number-1 ELSE p.partition_number END
    LEFT JOIN sys.destination_data_spaces DS ON DS.destination_id = P.Partition_Number AND DS.partition_scheme_id = PAS.data_space_id
    LEFT JOIN sys.data_spaces SDS ON SDS.data_space_id = DS.data_space_id
    LEFT JOIN sys.data_spaces NPSDS ON NPSDS.data_space_id = i.data_space_id
--WHERE O.[name] = 'Table'
ORDER BY O.[name], i.type_desc, i.[name], P.partition_number

 

If you are using SQL 2005 you should comment out the compression column.

Leave a Reply

Your email address will not be published. Required fields are marked *