{"id":47,"date":"2010-09-30T14:43:50","date_gmt":"2010-09-30T21:43:50","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=47"},"modified":"2010-10-11T16:29:21","modified_gmt":"2010-10-11T23:29:21","slug":"find-all-tables-without-a-primary-key","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2010\/09\/find-all-tables-without-a-primary-key\/","title":{"rendered":"Find All Tables Without A Primary Key"},"content":{"rendered":"<p>The following query will find all the tables in your database which are missing a primary key.\u00a0 In almost any situation a missing primary key is the result of an oversight or a design error and should be corrected.\u00a0 Since the INFORMATION_SCHEMA views are being utilized for this query it should be compatible with SQL Server 2000, SQL Server 2005, and SQL Server 2008. Query is as follows:<!--more--><\/p>\n<blockquote><p>SELECT<br \/>\nt.TABLE_CATALOG<br \/>\n,t.TABLE_SCHEMA<br \/>\n,t.TABLE_NAME<br \/>\n,t.TABLE_TYPE<br \/>\nFROM<br \/>\nINFORMATION_SCHEMA.TABLES t<br \/>\nLEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk<br \/>\nON\u00a0 t.TABLE_CATALOG = pk.TABLE_CATALOG<br \/>\nAND t.TABLE_NAME = pk.TABLE_NAME<br \/>\nAND t.TABLE_SCHEMA = pk.TABLE_SCHEMA<br \/>\nAND pk.CONSTRAINT_TYPE = 'PRIMARY KEY'<br \/>\nWHERE<br \/>\npk.TABLE_NAME IS NULL<br \/>\nAND t.TABLE_TYPE='BASE TABLE'<br \/>\nORDER BY<br \/>\nt.TABLE_CATALOG<br \/>\n,t.TABLE_SCHEMA<br \/>\n,t.TABLE_NAME<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>SQL script to find all the tables in a database which are missing a primary key.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-47","post","type-post","status-publish","format-standard","hentry","category-scripts"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/47","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=47"}],"version-history":[{"count":11,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/47\/revisions"}],"predecessor-version":[{"id":49,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/47\/revisions\/49"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=47"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=47"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=47"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}