Sql-server – How to find all tables in a DB that have no explicit primary key

primary-keysql serversql-server-2012

A Google search spewed forth millions of hits on how to find tables without clustered indexed, the PK normally being the clustered index of a table. However, a table could easily have a natural key as a clustered index, and a non-clustered surrogate index, like an identity column.

How do I find all tables in a DB without a primary key defined? I have 245 tables in this DB: manual inspection is grossly inefficient.

Best Answer

Couple ways to skin this cat but this works fine in SQL Server 2005 and up, and I find it a pain free way to handle the problem -

The OBJECTPROPERTY() function can list various properties about objects - like tables. One of those properties is whether or not a table has a primary key.

OBJECTPROPERTY(object_id, tablehasprimarykey) = 0 would be a table without a primary key.

SELECT OBJECT_SCHEMA_NAME( object_id ) as SchemaName, name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'tablehasprimaryKey') = 0 
ORDER BY SchemaName, TableName ;

Should give you what you need. You can see all about the other ways to use the OBJECTPROPERTY() function in books online. This is the 2012 version of the article.

Related Solutions

Sql-server – Is ‘Avoid creating a clustered index based on an incrementing key’ a theth from SQL Server 2000 days

The myth goes back to before SQL Server 6.5, which added row level locking. And hinted at here by Kalen Delaney.

It was to do with "hot spots" of data page usage and the fact that a whole 2k page (SQL Server 7 and higher use 8k pages) was locked, rather then an inserted row Edit, Feb 2012

Found authoritative article by Kimberly L. Tripp

"The Clustered Index Debate Continues..."

Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of page level locking (and this is where the term hot spot became a negative term). In fact, it doesn't have to be a negative term. However, since the storage engine was rearchitected/redesigned (in SQL Server 7.0) and now includes true row level locking, this motivation (to avoid hotspots) is no longer there.

Edit, May 2013

The link in lucky7_2000's answer seems to say that hotspots can exist and they cause issues. However, the article uses a non-unique clustered index on TranTime. This requires a uniquifier to be added. Which means the index in not strictly monotonically increasing (and too wide). The link in that answer does not contradict this answer or my links

On a personal level, I have woked on databases where I inserted tens of thousands of rows per second into a table that has a bigint IDENTITY column as the clustered PK.

PostgreSQL: Corrupt primary key, inconsistent table

There are various ways this can happen in Oracle - I'm not sure about postgres, but I think I would call this an "integrity violation" rather than "corruption"

Perhaps you can do one of the things suggested here, ie set enable_indexscan = off or

begin;
drop index rank_details_pkey;
select user_id, count(*) from rank_details group by user_id having count(*) > 1;
rollback;

But "there are likely some locking issues with this, so be careful with it in production"

The idea is to force the query to scan the table rather than just the index (which does not have the duplicates). You may also, and more simply, be able to acheive the same by:

select user_id, f(<some other column>), count(*)
from rank_details
group by user_id, f(<some other column>)
having count(*) > 1

where f() returns a constant, which may trick the planner into a table scan.

Best Answer

Related Solutions

Sql-server – Is ‘Avoid creating a clustered index based on an incrementing key’ a theth from SQL Server 2000 days

PostgreSQL: Corrupt primary key, inconsistent table

Related Question