Finding all tables that do not have a primary key | SQL Server Performance Forums

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’
(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
where Constraint_type=’Primary Key’ and table_name=T.table_name
) Madhivanan Failing to plan is Planning to fail