September 24, 2010 at 2:03 am
Hi,
Could some one post the query to find out Partitioned & non-partitioned Tables in SQL 2008/2005 ? I am finally able to find out by joining many tables. Is there any easy way to get it?
Any insight or quick info is highly appreciated.
Thanks
Neeraj
September 24, 2010 at 4:34 am
Try this:
Select c.name, b.name, CASE WHEN COUNT(*) > 1 THEN 'Yes' ELSE 'No' END 'Partitioned'
From sys.partitions a
Join sys.tables b on b.object_id = a.object_id
Join sys.schemas c on c.schema_id = b.schema_id
Where a.index_id <= 1
Group by c.name, b.name
Order by c.name, b.name
September 24, 2010 at 4:58 am
You can try the below query to get list of tables with partitions. Here, every table will have atleast one partition. So, partition number is 1-based partition number within the owning index or heap. For non-partitioned tables and indexes, the value of this column is 1. For partitioned number >1 means, it is either partitioned table or index. We are applying object property to check whether the object is indeed a table.
SELECT DISTINCT OBJECT_NAME([object_id]) FROM sys.partitions
WHERE partition_number > 1
AND OBJECTPROPERTY([object_id],'IsTable') = 1
Regards,
Venkat
September 24, 2010 at 5:11 am
Thanks Venkat & SSC for quick and prompt answere
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply