Search code examples
mysqlsqlmariadbinformation-schema

mysql/mariadb information_schema view creation time

I want to find all tables and views created before a certain timestamp. For tables it is easy, just

SELECT * FROM information_schema.tables
WHERE table_type = 'TABLE' AND create_time < :ts

But for views it is not so simple. The create_time column is null for all the views in information_chema.tables. e.g.

MariaDB [MYDB]> SELECT IF(create_time IS NULL, 'Null', 'Not Null') AS has_create_ts
                     , COUNT(1)
FROM information_schema.tables
WHERE table_type = 'VIEW' GROUP BY has_create_ts;
+---------------+----------+
| has_create_ts | COUNT(1) |
+---------------+----------+
| Null          |       70 |
+---------------+----------+
1 row in set, 10 warnings (0.371 sec)

And the information_schema.views table does not have any timestamp columns.

So how can I find out when a view was created? Or is it just not possible.

If it matters database version is:

MariaDB [MYDB]> SELECT VERSION();
+--------------------+
| VERSION()          |
+--------------------+
| 10.3.7-MariaDB-log |
+--------------------+
1 row in set (0.392 sec)
Solution

  • So how can I find out when a view was created? Or is it just not possible.

    No, this is not possible.

    A view does not actually contains data, it just made of a definition, ie a SQL statement that references data contained in real (physical) tables : hence this is all that INFORMATION_SCHEMA.VIEWS can show you.