From 78135241106b7bc4a69690952815d97450bfac02 Mon Sep 17 00:00:00 2001
From: Federico Razzoli <federico@ibuildings.it>
Date: Mon, 16 May 2016 00:36:52 +0200
Subject: [PATCH] view: empty_schema

---
 views/i_s/empty_schema.sql | 33 +++++++++++++++++++++++++++++++++
 1 file changed, 33 insertions(+)
 create mode 100644 views/i_s/empty_schema.sql

diff --git a/views/i_s/empty_schema.sql b/views/i_s/empty_schema.sql
new file mode 100644
index 0000000..7801878
--- /dev/null
+++ b/views/i_s/empty_schema.sql
@@ -0,0 +1,33 @@
+--
+-- View: empty_schema
+-- 
+-- A list of database that contain nothing (tables, routines, events).
+--
+-- mysql> SELECT * FROM empty_schema;
+-- +-------------+
+-- | schema_name |
+-- +-------------+
+-- | empty_sch   |
+-- +-------------+
+-- 1 row in set (0.01 sec)
+--
+
+CREATE OR REPLACE
+  ALGORITHM = TEMPTABLE
+  DEFINER = 'root'@'localhost'
+  SQL SECURITY INVOKER 
+VIEW empty_schema (
+  schema_name
+) AS
+SELECT all_schemas.SCHEMA_NAME
+  FROM information_schema.SCHEMATA all_schemas
+  LEFT JOIN (
+    SELECT DISTINCT ROUTINE_SCHEMA AS object_schema FROM information_schema.ROUTINES
+  UNION 
+    SELECT DISTINCT TABLE_SCHEMA AS object_schema FROM information_schema.TABLES
+  UNION
+    SELECT DISTINCT EVENT_SCHEMA AS object_schema FROM information_schema.EVENTS
+  ) used_schemas
+    ON used_schemas.object_schema = all_schemas.SCHEMA_NAME
+    WHERE used_schemas.object_schema IS NULL
+  ORDER BY SCHEMA_NAME;