query for: is table X partitioned?

bravegag · *nix forums beginner Joined: 12 Apr 2006 Posts: 16

Hi all,

Our project has a large number of tables and we need some generic
method to truncate them all (dropping tables is out of the question),
the problem is that some of the tables are partitioned and some
aren't. I can implement the feature above easily as a stored procedure
but I still miss the bit to tell whether a table is partitioned or not
.... needed because you need to unpartition it before truncating and
then repartition it back to the same number it was before.

I need a query that has the following interface:

select ispartitioned (e.g. 'partitioned', 'unpartitioned'),
numpartitions (e.g. 5)
from ...
where object_id('table_name') = id

Thanks in advance,
Best Regards,
Giovanni

DBAGAL · *nix forums beginner Joined: 31 May 2005 Posts: 39

Hi Giovanni,

select object_name(id), numpartitions=count(*)
from syspartitions
group by object_name(id)

This works for pre ASE 15.0 systems ... the syspartitions table has
been completely redone for 15.0.

Cheers,

Sara ...

bravegag wrote:

amarnadh@iitb.ac.in · *nix forums beginner Joined: 14 May 2005 Posts: 2

Hi ,

select object_name(id), count(*) partitoned
from syspartitions
where indid = 0 or indid = 1
group by id, indid
having count(*) > 1

This query will give all the partitioned tables in the database. (for
ASE - 15.0)

select object_name(id), count(*) partitoned
from syspartitions
where indid = 0 or indid = 1
group by id, indid
having count(*) = 1

This query will give all the un-partitioned tables in the database (for
ASE-15.0)

You can extend these queries as per your requirements.

Thanks,
Amarnadh.

DBAGAL wrote:

Google