LearnItFirst User Forum

/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script lists all tables without a primary key
   
    Versions: SQL Server 2005, 2000, 7.0
   
    Creation Date: August 28, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
SELECT
   T.TABLE_NAME AS "Tables without PKs"
FROM INFORMATION_SCHEMA.TABLES AS T
WHERE NOT EXISTS
   (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
      WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
         AND T.TABLE_NAME = TC.TABLE_NAME)
         AND T.TABLE_TYPE = 'BASE TABLE'