Tag Archives: sys.indexes

Transact-SQL query to obtain basic information about partitioned tables

Here is the simple query that returns basic information about all tables in a database that are partitioned:

SELECT SCHEMA_NAME([schema_id]) AS [schema_name]
      ,t.[name] AS [table_name]
      ,i.[name] AS [index_name]
      ,i.[type_desc] AS [index_type]
      ,ps.[name] AS [partition_scheme]
      ,pf.[name] AS [partition_function]
      ,p.[partition_number]
      ,r.[value] AS [current_partition_range_boundary_value]
      ,p.[rows] AS [partition_rows]
      ,p.[data_compression_desc]
FROM sys.tables t
INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id]
                           AND p.[index_id] = i.[index_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.partition_schemes ps ON ds.[data_space_id] = ps.[data_space_id]
INNER JOIN sys.partition_functions pf ON ps.[function_id] = pf.[function_id]
LEFT JOIN sys.partition_range_values AS r ON pf.[function_id] = r.[function_id]
    AND r.[boundary_id] = p.[partition_number]
GROUP BY SCHEMA_NAME([schema_id])
        ,t.[name]
        ,i.[name]
        ,i.[type_desc]
        ,ps.[name]
        ,pf.[name]
        ,p.[partition_number]
        ,r.[value]
        ,p.[rows]
        ,p.[data_compression_desc]
ORDER BY SCHEMA_NAME([schema_id])
        ,t.[name]
        ,i.[name]
        ,p.[partition_number];

The query returns the following columns:

  • schema_name – name of the partitioned table schema.
  • table_name – name of the partitioned table.
  • index_name – name of the partitioned index.
  • index_type – type of the partitioned index.
  • partition_scheme – name of the partition scheme.
  • partition_function – name of the partition function.
  • partition_number – indicate the partition number.
  • current_partition_range_boundary_value – partition actual boundary value.
  • partition_rows – indicates approximate number of rows in current partition.
  • data_compression_desc – Indicates the state of compression for each partition.

This example query uses following system views: sys.tables, sys.partitions, sys.indexes, sys.partition_schemes, sys.partition_functions and sys.partition_range_values.

For more information on table partitioning, see “Partitioned Tables and Indexes” on MSDN website.

Advertisement

List all Objects and Indexes per Filegroup / Partition

Today, I received a call from friend asking how he can find out which database object belongs to which filegroup.

Well to find out this information can be achieved by writing a simple query using the following system catalogues: sys.filegroups, sys.indexes, sys.database_files and sys.data_spaces.

For example, here is my version of the query to return this information:

-- The following two queries return information about 
-- which objects belongs to which filegroup
SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName]
	,i.[index_id] AS [IndexID]
	,i.[name] AS [IndexName]
	,i.[type_desc] AS [IndexType]
	,i.[data_space_id] AS [DatabaseSpaceID]
	,f.[name] AS [FileGroup]
	,d.[physical_name] AS [DatabaseFileName]
FROM [sys].[indexes] i
INNER JOIN [sys].[filegroups] f
	ON f.[data_space_id] = i.[data_space_id]
INNER JOIN [sys].[database_files] d
	ON f.[data_space_id] = d.[data_space_id]
INNER JOIN [sys].[data_spaces] s
	ON f.[data_space_id] = s.[data_space_id]
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
ORDER BY OBJECT_NAME(i.[object_id])
	,f.[name]
	,i.[data_space_id]
GO

See the sample output generated by this query, when I executed against AdventureWork2012 sample database.

ObjectsInFileGroups

I hope you will find the information return by this query useful.