Detect last table created in sqlite?

Targets Web
1

In my web application,
How do I retrieve the name of the last table created (sqlite)?
Thanks to you…

2

No idea.

But, at file creation add code with a Date COLUMN and set the Date of the TABLE creation.

More information about SQLite:
SQLite.org
SQLite Tutorial

3

If you need that, take a note in a table created to control your dynamic system, with columns like table_name, creation_datetime, etc and store the data you will use later.

If you are creating dynamically lots of tables, specially continuously, you should rethink your design, it may be a wrong design.

1 Like
4

Is this what you are looking for?

SELECT name FROM sqlite_master 
WHERE type ='table' AND name NOT LIKE 'sqlite_%' 
ORDER BY rowid DESC 
LIMIT 1;
2 Likes
6

That’s why his SQL is NOT LIKE, so that it skips the internal table names.

7

alternate idea is via trigger and user defined function (i found at stackoverflow)
in the trigger you could memory anything at the creation in other Table like Date,User,Session,LastTableName

CREATE TRIGGER ...
...
BEGIN
    SELECT my_create_table_function(NEW.name);
END;
1 Like
8

This approach is not future proof and risky. Imagine for example that at some point you will need a new functionality in your system needing a new table. Like user_ address_table. That new table will be grabbed in error by the routine watching “new tables to process”. Better you control your queue, names, properties, statuses, etc aside writing your control data in a proper control table to avoid unexpected future clashes and being able to add more control properties over time.

1 Like
9

Thank you all for your help, but I’m going to review the way I manage my database.