When SQL Server first introduced table and index partitioning in SQL 2005, it was the same version that all the metadata changed. So I decided that I could learn a lot about metadata and a lot about the organization and storage of partitions if I tried to write a query, and then a view, that included almost everything anyone would ever need to know about any partitioned objects you had created. While doing that I realized that one function that SQL Server was missing, in spite of all the incredible new metadata and built-in functions, was a function to return the name of an index, given the object_id and the index_id. Since so much of the metadata contains these ID columns, it seems like a useful thing. But I had to write one for myself:
CREATE FUNCTION dbo.index_name (@object_id int, @index_id tinyint)
RETURNS sysname
AS
BEGIN
DECLARE @index_name sysname
SELECT @index_name = name FROM sys.indexes
WHERE object_id = @object_id and index_id = @index_id
RETURN(@index_name)
END;
This function needs to be created in the database where you want to use it.
I wrote the view to return all the partitioning info to be used in my classes. It took quite a while, and was not perfect. The two pieces I kept meaning to fix were:
- It would return no rows for a table that was not partitioned at all
- It would only list one boundary point for each partition. This meant you always had to return the partitions in order in order to see the lower and upper boundary for each. However, one of my students in a class I taught in Salt Lake City in September 2009 took on the task of 'fixing' those problems, so the version I show you here is the updated one.
I decided to make this script public today in response to a query on the public forums. I'm not including a script that will create a partitioned table for you. If you don't have a partitioned table of your own, you probably won't even be interested in this script!
-- Create a view to return details about a partitioned table or index
-- First run the script to create the function index_name()
-- Written by Kalen Delaney, 2008
-- with a few nice enhancements by Chad Crawford, 2009
CREATE VIEW Partition_Info AS
SELECT OBJECT_NAME(i.object_id) as Object_Name, dbo.INDEX_NAME(i.object_id,i.index_id) AS Index_Name,
p.partition_number, fg.name AS Filegroup_Name, rows,
au.total_pages,
CASE boundary_value_on_right
WHEN 1 THEN 'less than'
ELSE 'less than or equal to'
END as 'comparison'
, rv.value,
CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A'
ELSE
CASE
WHEN boundary_value_on_right = 0 AND rv2.value IS NULL
THEN 'Greater than or equal to'
WHEN boundary_value_on_right = 0
THEN 'Greater than'
ELSE 'Greater than or equal to' END + ' ' +
ISNULL(CONVERT(varchar(15), rv2.value), 'Min Value')
+ ' ' +
+
CASE boundary_value_on_right
WHEN 1 THEN 'and less than'
ELSE 'and less than or equal to'
END + ' ' +
+ ISNULL(CONVERT(varchar(15), rv.value),
'Max Value')
END as 'TextComparison'
FROM sys.partitions p
JOIN sys.indexes i
ON p.object_id = i.object_id and p.index_id = i.index_id
LEFT JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.partition_functions f
ON f.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv
ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
LEFT JOIN sys.partition_range_values rv2
ON f.function_id = rv2.function_id
AND p.partition_number - 1= rv2.boundary_id
LEFT JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN sys.allocation_units au
ON au.container_id = p.partition_id
WHERE i.index_id <2 AND au.type =1
-- Example of use:
SELECT * FROM Partition_Info
WHERE Object_Name = 'charge'
ORDER BY Object_Name, partition_number
Have fun!
~Kalen