Getting All Table Information

One thing that bugs me in SQL Server is how hard it is to get information about your tables to analyse usage, indexes and size.

This is a query I wrote several years ago and still use today. Information exported includes:

  • All Index information including columns
  • Compression
  • File Groups
  • Space Used
  • Row Count
  • Index Usage
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, CAST(PS.lob_reserved_page_count * 8 as money)/1024 LOB_SpaceReserved,  
          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,   
          LEFT(IndexColumns.IndexColumns,LEN(IndexColumns.IndexColumns)-1) IndexColumns,  
          LEFT(IndexColumnsIncluded.IndexColumnsIncluded,LEN(IndexColumnsIncluded.IndexColumnsIncluded)-1) 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  
          OUTER APPLY (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 (IndexColumns)  
          OUTER APPLY (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(IndexColumnsIncluded)  
     --WHERE O.[name] = 'Account'  
     ORDER BY O.[name], i.type_desc, i.[name], P.partition_number  

An example output is shown here:

Click image to enlarge

Click image to enlarge


See also