Sunday 17 April 2011

ASE : How to find out tables missing primary key

Before setup replication server, we need to find out which table is missing primary key. I used following query to find out

use dbname
go

select name
from sysobjects
where id not in (select id from syskeys where type = 1)
and type = 'U'
go

So many tables are missing primary key, then I try to reduce the list by checking if they have unique index using following query

select o.name
from sysobjects o
where o.id not in (select id from syskeys where type = 1)
and o.type = 'U'
and o.id not in (select id from sysindexes where status & 2 = 2 )
order by o.name

the list of tables is reduced to half about 55. Then I further reduce the list by checking the size of each table.

select distinct o.name, rowcnt(i.ioampg)
from sysobjects o, sysindexes i
where o.id not in (select id from syskeys where type = 1)
and o.type = 'U'
and o.id not in (select id from sysindexes where status & 2 = 2 )
and o.id = i.id
order by rowcnt(i.ioampg)
go

Take out all the empty tables. Now the list is short and I can work on the few tables (adding PK etc).

1 comment:

  1. Hi,
    What is the significance of syskeys in a database.
    I created 2 tables with the primary key constraint but I did not see the entries in syskeys table.

    I think your query to find out P keys from syskeys does not return correct values.

    ReplyDelete