The first step is to click the 'Create new model' button and then pick 'Physical data model'. Please make sure you have either the SQLite command line interface or SQLite Database Browser installed on your platform. See PRAGMA table_list in SQLite for an overview and examples.In this tutorial we will cover the process of creating a working SQLite DB based on a Vertabelo project. Since I first wrote this article, SQLite has introduced the table_list pragma statement, which lists out tables and views: PRAGMA table_list ORDER BY name Bonus 3rd Option: The table_list Pragma Statement
To return both permanent tables and temporary tables, you can use a query like this: SELECT name FROM If you need to return just the temporary tables, you can query sqlite_temp_schema or its synonym sqlite_temp_master. The sqlite_schema table only contains permanent tables. table command returns both permanent tables and temporary tables.
Now query sqlite_schema for tables only: SELECT name FROM sqlite_schema Query the sqlite_schema table for tables and views: SELECT name FROM sqlite_schema This database contains one table (called Products) and one view (called vProducts).Ĭonnect to SQLite/the database: sqlite3 Store.db Exclude Viewsįor the sake of completeness, here’s a quick example that uses a database with a view. The sqlite_schema table can also be accessed by using sqlite_master. In my case there aren’t any views, but if you want to exclude views in the results, use this: SELECT name FROM sqlite_schema That query returns both tables and views (just as the. Running the above query returns the following result: Album tables command returns results for all attached databases). The difference is that this method only returns results for the primary database (the. tables command, it’s similar to doing this: SELECT name FROM sqlite_schema The sqlite_schema TableĮvery SQLite database has an sqlite_schema table that defines the schema for the database. You can use this table to return a list of tables in your database. Although this table also contains views, you can use SQL to exclude them from your results if need be. This will only work if your views use a naming convention that distinguishes them from tables and other objects.Īnother way to exclude views from your results is to query the sqlite_schema table directly. If you want to exclude views from your results, you could use pattern matching to exclude views. tables command returns both tables and views. In this case only tables that begin with the letter “a” are returned. For example, you can name a specific table, or you can use pattern matching to return only tables that match a given pattern. Such an argument can be used to limit the tables returned by the command. In my case, there’s only one attached database (the Chinook sample database), and all of this database’s tables are returned.Īs mentioned, you can also provide an argument to this command. Result: Album Employee InvoiceLine PlaylistTrack If you use it without providing an argument, it returns all tables (and views) for all attached databases. This command can be used with or without an argument. The easiest way to return a list of tables when using the SQLite command line shell is to use the.
Update Dec 2021: Since writing this article, SQLite has introduced another option, which I’ve listed as a bonus third option at the end of this article. The second method gives you the option of returning both tables and views, or just tables, but only for the primary database. The first method returns all tables and views for all attached databases. Here are two ways to return a list of tables in all attached databases in SQLite.