How to find out table is partitioned ?

  • 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

  • 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

  • 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

  • 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