May 20, 2008 at 2:34 am
Hi,
Is there any way to check if a table is partitioned or not? I'm asking this because i need to create a script that renames a table, recreates the table as a partitioned table, and then swaps the renamed table with partition 1 of the partitioned table.
It is very important that this script is only executed once so i would like to include a check in the script.
I tried consulting the sys tables, and at first i thought i could simply check the partition table to see how many partitions a table has. However, after some querying i found that tables that were never partitioned actually had more than one related record in the partitions table!
I can only assume that sql server can decide for itself to split the underlying data ( disk fragmentation ? ).
Anyway, if anyone knows a solid way to check if a table is partitioned ...
May 20, 2008 at 3:36 am
How do you check if a table is partitioned?
Try this query:
select schema_name(c.schema_id) [schema name],
object_name(a.object_id)
,
a.name [index name], a.type_desc [index type]
from (sys.indexes a inner join sys.tables c
on a.object_id = c.object_id)
inner join sys.data_spaces b on a.data_space_id = b.data_space_id
where b.type='PS'
If you have more than one index on a table, table will be referenced more than once in such query. You have to decide how to filter them.
HTH
Piotr
...and your only reply is slàinte mhath
May 20, 2008 at 4:59 am
/*
show partitioned objects
*/
select distinct
p.[object_id],
TbName = OBJECT_NAME(p.[object_id]),
index_name = i.[name],
index_type_desc = i.type_desc,
partition_scheme = ps.[name],
data_space_id = ps.data_space_id,
function_name = pf.[name],
function_id = ps.function_id
from sys.partitions p
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
-- WHERE p.[object_id] = object_id('JBMTest')
order by TbName, index_name ;
GO
/*
show partitioned objects range values
*/
select p.[object_id],
OBJECT_NAME(p.[object_id]) AS TbName,
p.index_id,
p.partition_number,
p.rows,
index_name = i.[name],
index_type_desc = i.type_desc,
i.data_space_id,
ds1.NAME AS [FILEGROUP_NAME],
pf.function_id,
pf.[name] AS Pf_Name,
pf.type_desc,
pf.boundary_value_on_right,
destination_data_space_id = dds.destination_id,
prv.parameter_id,
prv.value
from sys.partitions p
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
inner join sys.destination_data_spaces dds
on dds.partition_scheme_id = ds.data_space_id
and p.partition_number = dds.destination_id
INNER JOIN sys.data_spaces ds1
on ds1.data_space_id = dds.data_space_id
left outer JOIN sys.partition_range_values prv
on prv.function_id = ps.function_id
and p.partition_number = prv.boundary_id
--WHERE p.[object_id] = object_id('thename')
order by TbName, p.index_id, p.partition_number
;
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 6, 2011 at 10:58 am
This should do it I think ;
select distinct object_name(object_id) from sys.partitions
group by object_id, index_id
having COUNT(*) > 1
Basically a table and its indexes are represented in sys.partitions. For each object & index id if there is more than 1 row then the table is partitioned. Unless there is another reason, other than partitioning, why there is more than 1 row.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply