SQL Server - checking if any tables don't have a primary key

SQL

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 TableNameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0ORDER BY SchemaName, TableName;

Previous
Previous

PC Pro Podcast 282

Next
Next

SQL Server - List all tables and the fragmentation percentage