SQL Server – checking if any tables don’t have a primary key

Its easily done, in the rush to create tables someone forgets to tell the auto-number field that it is also a primary key.

Its not an issue until the database gets big, at which point (as listed on Microsoft TechNet) the performance can suffer by anything between 13% and 460% – which is potentially crippling for your database.

Thankfully there is a simple SQL script you can run to double-check that there are no tables without a primary key.

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
ORDER BY SchemaName, TableName;

Posted in: SQL

Leave a Reply