Identifying Partitioned Indexes

[MissTipps]

Ok, so I have a stored procedure that 'intelligently' does my DB optimisation. It uses the dm_db_index_physical_stats DMF to populate a table with details of all indexes that need a rebuild/reorg. Then from that it dynamically builds and executes the necessary ALTER INDEX statements.

Thats all well and good, until someone migrates a DB into production that contains partitioned tables/indexes. This brings the added complication of specifying the partition in the ALTER INDEX statement. Of course I only want to pass the PARTITION= parameter if the index being rebuilt is partitioned (otherwise it will fail), but am I being completely stupid because I can't figure out where the heck in any system object there is an 'is_partitioned' value.

I thought maybe the sys.partitions table would be the key, but no, because every table/index has a partitionID, whether its partitioned or not. I thought there would be a cool ISPARTITIONED() function out there that I could use...there is not.

I really hope I'm being dumb and someone will quickly set me back on my rails.

Thanks

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[mkal]

May be this could help? There is a lot to review but you might find what your looking for in it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2865833&SiteId=1

Best Regards,
Mike

 

[MissTipps]

Thanks Mike. It doesn't get to the bottom of the problem, but is basically the way I thought I would have to go with this, i.e. count the number of partitions.

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+