What are all the tables without a primary key?

This is specific to Microsoft SQL Server 2005

  • All tables SHOULD have a primary key.
  • Primary keys are the fastest and simplest way (for MS SQL Server) to retrieve the data.
  • You should have a reason to explain for why, you would not have a primary unique key on a table.

Summary

How to get the list of all tables without any primary key with MS SQL server 2005?

  • You cannot view all the tables when using Visual Studio, you must do it one table at a time.

Primary keys missing

use sql911;
go

SELECT SCHEMA_NAME(schema_id) AS "Schema", name AS "Table"
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY 1,2;
go

Schema    Table
--------- -------------
dbo       DEPARTMENT
dbo       EMP_WORK
dbo       Emps
dbo       sales
dbo       sales02

(5 row(s) affected)
  • Do not forget to first select the database that you want to use.
  • This will give you all the tables without a primary key.
  • This includes all the schemas.
  • After this you should create a primary, unique key for these tables.