September 1, 2011 at 1:32 pm
I have couple questions regarding the partitions.
1.If a table is partitioned where Can i see how its partitioned (the criteria for partitions) and partition names?
2.Where can I find the partitions created for a view?
Thanks.
September 1, 2011 at 4:23 pm
I am new to partitions but this may be a start:
SELECT b.NAME, a.*
FROM sys.partitions a
INNER JOIN sys.sysobjects b ON a.object_id = b.id
WHERE b.xtype = 'u'
ORDER BY b.name
September 1, 2011 at 4:27 pm
This will tell you the ranges in your partitions:
SELECT a.NAME, a.boundary_value_on_right, b.boundary_id, b.value
FROM sys.partition_functions a
INNER JOIN sys.partition_range_values b ON a.function_id = b.function_id
ORDER BY a.name
September 1, 2011 at 4:32 pm
Regarding your second question, if it refers to partitioned views then the range etc. would be in the definition of the view. That is: sp_helptext nameofview
September 2, 2011 at 2:24 am
Something like this.
DECLARE @Fanout int
DECLARE @FunctionID int
SELECT @Fanout = fanout -1, @FunctionID = function_id FROM sys.partition_functions
WHERE name = 'function'
SELECT sp.Partition_Number, sp.rows, '< ' + Cast(spv1.value as varchar(12)) as PartitionRange
FROM sys.partitions sp
JOIN sys.partition_range_Values spv1 on spv1.boundary_id = sp.Partition_Number AND spv1.function_id = @FunctionID
LEFT JOIN sys.partition_range_values spv2 on spv2.function_id = spv1.function_id AND spv1.boundary_id = spv2.boundary_id + 1 AND spv2.function_id = @FunctionID
WHERE [object_id] = object_id('function')
AND index_id = 1
AND Partition_Number = 1
UNION
SELECT sp.Partition_Number, sp.rows, '>= ' + isnull(cast(spv2.[Value] as varchar(12)),'0') + ' AND < ' + Cast(spv1.value as varchar(12)) as PartitionRange
FROM sys.partitions sp
JOIN sys.partition_range_Values spv1 on spv1.boundary_id = sp.Partition_Number AND spv1.function_id = @FunctionID
LEFT JOIN sys.partition_range_values spv2 on spv2.function_id = spv1.function_id AND spv1.boundary_id = spv2.boundary_id + 1 AND spv2.function_id = @FunctionID
WHERE [object_id] = object_id('function')
AND index_id = 1
AND Partition_Number > 1
UNION
SELECT sp.Partition_Number + 1, sp.rows, '>= ' + isnull(cast(spv1.[Value] as varchar(12)),'0') as PartitionRange
FROM sys.partitions sp
JOIN sys.partition_range_Values spv1 on spv1.boundary_id = sp.Partition_Number AND spv1.function_id = @FunctionID
LEFT JOIN sys.partition_range_values spv2 on spv2.function_id = spv1.function_id AND spv1.boundary_id = spv2.boundary_id + 1 AND spv2.function_id = @FunctionID
WHERE [object_id] = object_id('function')
AND index_id = 1
AND Partition_Number = @Fanout
September 2, 2011 at 2:36 am
I've not tried to write a generic query to show how the data is distributed in a partitioned table - here's a query I use - you'll need to add your own data for all the places there are #xxxx# - what the query doe sis show the partition number, rows in each partition and min and max values for two columns in each partition, obviously this is taken from one of my partitioned tables so you'll need to modify it
SELECT 'dbo.#tablename#' as [TableName],$partition.[#partition_function#](o.#partition_column#)
AS [Partition Number]
, min(o.#a_date#) AS [Min Date]
, max(o.#a_date#) AS [Max Date]
,min(o.#ID_range#) as [Min ID]
,max(o.#ID_range#) as [Max ID]
, count(*) AS [Rows In Partition]
FROM dbo.#tablename# AS o
GROUP BY $partition.[#partition_function#](o.#partition_column#)
I have a document based upon a presentation I did which may help answer some of your questions www.grumpyolddba.co.uk/gonein60ns/GoneIN60nsWeb.pdf
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply