{"id":215,"date":"2017-03-20T13:52:43","date_gmt":"2017-03-20T13:52:43","guid":{"rendered":"http:\/\/techref.passionwind.org\/?p=215"},"modified":"2017-05-18T16:52:29","modified_gmt":"2017-05-18T20:52:29","slug":"finding-primary-keys-and-missing-primary-keys-in-sql-server","status":"publish","type":"post","link":"http:\/\/techref.camellarry.com\/?p=215","title":{"rendered":"SQL Finding primary keys and missing primary keys"},"content":{"rendered":"<h1>Tables with Primary Keys<\/h1>\n<pre><span style=\"color: #0000ff;\">SELECT c.name, b.name, a.name<\/span>\r\n<span style=\"color: #0000ff;\"> FROM sys.key_constraints a<\/span>\r\n<span style=\"color: #0000ff;\"> INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID<\/span>\r\n<span style=\"color: #0000ff;\"> INNER JOIN sys.schemas c ON a.schema_id = c.schema_id<\/span>\r\n<span style=\"color: #0000ff;\"> WHERE a.type = 'PK'<\/span><\/pre>\n<h1>Tables Without Primary Keys<\/h1>\n<pre><span style=\"color: #0000ff;\">SELECT c.name, b.name<\/span>\r\n<span style=\"color: #0000ff;\"> FROM sys.tables b<\/span>\r\n<span style=\"color: #0000ff;\"> INNER JOIN sys.schemas c ON b.schema_id = c.schema_id<\/span>\r\n<span style=\"color: #0000ff;\"> WHERE b.type = 'U'<\/span>\r\n<span style=\"color: #0000ff;\"> AND NOT EXISTS<\/span>\r\n<span style=\"color: #0000ff;\"> (SELECT a.name<\/span>\r\n<span style=\"color: #0000ff;\"> FROM sys.key_constraints a<\/span>\r\n<span style=\"color: #0000ff;\"> WHERE a.parent_object_id = b.OBJECT_ID<\/span>\r\n<span style=\"color: #0000ff;\"> AND a.schema_id = c.schema_id<\/span>\r\n<span style=\"color: #0000ff;\"> AND a.type = 'PK' )<\/span><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Tables with Primary Keys SELECT c.name, b.name, a.name FROM sys.key_constraints a INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID INNER JOIN sys.schemas c ON a.schema_id = c.schema_id WHERE a.type = &#8216;PK&#8217; Tables Without Primary Keys SELECT c.name, b.name FROM sys.tables b INNER JOIN sys.schemas c ON b.schema_id = c.schema_id WHERE b.type = &#8216;U&#8217; AND NOT [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8],"tags":[70,72],"class_list":["post-215","post","type-post","status-publish","format-standard","hentry","category-sql","tag-sql","tag-syntax"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9t3oE-3t","_links":{"self":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts\/215","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=215"}],"version-history":[{"count":2,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts\/215\/revisions"}],"predecessor-version":[{"id":226,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts\/215\/revisions\/226"}],"wp:attachment":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=215"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=215"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=215"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}