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).
Hi,
ReplyDeleteWhat 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.