From ce66989225c3df0859d94005366f9a512574aad0 Mon Sep 17 00:00:00 2001
From: Federico Razzoli <federico@ibuildings.it>
Date: Fri, 6 May 2016 17:03:49 +0200
Subject: [PATCH] view: table_without_primary_key

---
 sys_56.sql                              |  1 +
 sys_57.sql                              |  1 +
 views/i_s/table_without_primary_key.sql | 48 +++++++++++++++++++++++++++++++++
 3 files changed, 50 insertions(+)
 create mode 100644 views/i_s/table_without_primary_key.sql

diff --git a/sys_56.sql b/sys_56.sql
index afa34bc..6f3df97 100644
--- a/sys_56.sql
+++ b/sys_56.sql
@@ -54,6 +54,7 @@ SOURCE ./views/i_s/schema_object_overview.sql
 SOURCE ./views/i_s/schema_auto_increment_columns.sql
 SOURCE ./views/i_s/x_schema_flattened_keys.sql
 SOURCE ./views/i_s/schema_redundant_indexes.sql
+SOURCE ./views/i_s/table_without_primary_key.sql
 
 SOURCE ./views/p_s/ps_check_lost_instrumentation.sql
 SOURCE ./views/p_s/processlist.sql
diff --git a/sys_57.sql b/sys_57.sql
index 8e25b9c..e67cdd1 100644
--- a/sys_57.sql
+++ b/sys_57.sql
@@ -55,6 +55,7 @@ SOURCE ./views/i_s/schema_object_overview.sql
 SOURCE ./views/i_s/schema_auto_increment_columns.sql
 SOURCE ./views/i_s/x_schema_flattened_keys.sql
 SOURCE ./views/i_s/schema_redundant_indexes.sql
+SOURCE ./views/i_s/table_without_primary_key.sql
 
 SOURCE ./views/p_s/ps_check_lost_instrumentation_57.sql
 
diff --git a/views/i_s/table_without_primary_key.sql b/views/i_s/table_without_primary_key.sql
new file mode 100644
index 0000000..b405c5f
--- /dev/null
+++ b/views/i_s/table_without_primary_key.sql
@@ -0,0 +1,48 @@
+-- Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
+--
+-- This program is free software; you can redistribute it and/or modify
+-- it under the terms of the GNU General Public License as published by
+-- the Free Software Foundation; version 2 of the License.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
+
+--
+-- View: table_without_primary_key
+--
+-- Tables that don't have a Primary Key.
+-- Note that, for InnoDB tables, this does not necessarily mean that a clustered index is automatically generated.
+--
+-- mysql> SELECT * FROM table_without_primary_key WHERE table_schema = 'test' LIMIT 3;
+--          +--------------+------------+--------+
+--          | table_schema | table_name | engine |
+--          +--------------+------------+--------+
+--          | test         | t          | InnoDB |
+--          | test         | user_ax    | InnoDB |
+--          | test         | user_bd    | InnoDB |
+--          +--------------+------------+--------+
+--          3 rows in set (0.05 sec)
+--
+
+CREATE OR REPLACE
+  ALGORITHM = MERGE
+  DEFINER = 'root'@'localhost'
+  SQL SECURITY INVOKER
+VIEW table_without_primary_key (
+  table_schema,
+  table_name,
+  engine
+) AS
+SELECT
+    t.TABLE_SCHEMA, t.TABLE_NAME, t.ENGINE
+  FROM information_schema.COLUMNS c
+  RIGHT JOIN information_schema.TABLES t
+    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME AND c.COLUMN_KEY = 'PRI'
+  WHERE
+    c.COLUMN_NAME IS NULL;