SQL Server Performance Forum – Threads Archive

Finding all tables that do not have a primary key

I have a request to set up transaction replication on a third party database to create a reporting server. Unfortunately, many of the tables do not have a primary key so can not participate in transaction replication. The database contains several hundred tables and I was looking for an EASY way to list all tables that do not have a primary key. I ended up populating a cursor with rows selected from sysobjects (xtype = ‘U’), passing the tablenames one at a time into sp_pkeys, and storing the results in a temporary table. I then joined the temporary table to the sysobjects table to identify those tables that had no primary key. This works, but fails my easy test. Is there an easier way?

---

With the usual caveats about querying system tables … SELECT o.[name] FROM dbo.sysobjects o
WHERE o.xtype = ‘U’
AND o.id NOT IN
(SELECT pk.parent_obj FROM dbo.sysobjects pk WHERE pk.xtype = ‘PK’)

---

Other solution
Select table_name from INFORMATION_SCHEMA.TABLES T
where table_type=’Base Table’
and not exists
(
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where Constraint_type=’Primary Key’ and table_name=T.table_name
) Madhivanan Failing to plan is Planning to fail

---

Thanks.

---

]]>