Sybase Tables

[Former Member]

Hi,

How can I see number of partitions in a table?

Regards,

Vrushali

[jong-kil_park]

Hi,

You can also run a query below to see the information of a partitioned table.

/**************************************************************************************************/

select	Upper(P.partition_name) partition_name, P.partition_values, DBS.dbspace_name
from	SYSPARTITIONS P, SYSTABLE T, SYSDBSPACE DBS
where	P.table_id = T.table_id
and	P.partition_dbspace_id = DBS.dbspace_id

and t.table_name='part_order'

-- suser_name(t.creator) = 'table_owner'

order by p.partition_id

/**************************************************************************************************/

You will see this kind of results.

partition_name,partition_values,     dbspace_name

'P1',                '1992-12-31',          'DSP1'

'P2',                '1993-12-31',          'DSP1'

'P3',                '1994-12-31',          'DSP1'

'P4',                '1995-12-31',          'DSP1'

'P5',                '1996-12-31',          'DSP1'

'P6',                '1997-12-31',          'DSP1'

'P7',                '1998-12-31',          'DSP1'

'P82',                MAX,                   'DSP1'

'P81',               '1999-12-31',          'DSP1'

Best Regards

Jerry

[Former Member]

Hi Jerry,

Thanks for the query.

I tried to run this query but I get -

'Empty result set fetched'.

Any idea what is causing this warning?

Appreciate your response. Thanks.

[Former Member]

Just realized my syspartitions table is blank.

Is there any other alternative where partitions are stored?

[Former Member]

It does sound like your tables have no partitions defined. If you have some doubts, you can generate the DDL for your tables.

[saroj_bagai]

sp_iqtable <tablename>

[Former Member]

Hi Saroj,

This does not seem to work for me.

Is there a format for tablename?

Can we run select on SP to see what kind of values it has?

[Former Member]

select object_name, partitions from sp_iqdbspaceobjectinfo() where object_type='table';

[Former Member]

Hi Eric,

Thanks for the sql.

This does run. but the value is partitions column is '0/0' for all object_names.

How can I count number of partitions for a particular table?

[Former Member]

The output is the no. partitions in this dbspace/ total no. of partitions for the table.

select partitions from sp_iqdbspaceobjectinfo() where object_name='<your_table_name>';

0/0 indicates the table has no partitioning.

[Former Member]

Hi Eric,

Thanks for your response.

But in database I see table is partitioned as tablename_01, tablename_02 and so on

This information must exist somewhere.. right?

How can I generate DDL? Do I need any tool for it?