tables command or by querying data from the sqlite_schema table. In this tutorial, you have learned how to show all tables in a database using the. Note that SQLite changed the table sqlite_master to sqlite_schema. These tables are the system tables managed internally by SQLite. In this query, we filtered out all tables whose names start with sqlite_ such as sqlite_stat1 and sqlite_sequence tables. WHERE type = 'table' AND name NOT LIKE 'sqlite_%' Code language: SQL (Structured Query Language) ( sql ) The output is as follows: playlist_track tracks Code language: SQL (Structured Query Language) ( sql ) Showing tables using SQL statementĪnother way to list all tables in a database is to query them from the sqlite_schema table. tables '%ck%' Code language: SQL (Structured Query Language) ( sql ) To shows the tables whose name contains the string ck, you use the %ck% pattern as shown in the following command. Here is the output: albums artists Code language: plaintext ( plaintext ) table 'a%' Code language: SQL (Structured Query Language) ( sql ) The pattern must be surrounded by single quotation marks ( ').įor example, to find tables whose names start with the letter ‘a’, you use the following command. The command works the same as LIKE operator. tables pattern Code language: SQL (Structured Query Language) ( sql ) If you want to show tables with the specific name, you can add a matching pattern. The following shows the output: albums employees invoices playlistsĪrtists genres media_types temp.temp_table1Ĭustomers invoice_items playlist_track tracks Code language: SQL (Structured Query Language) ( sql )īecause the schema of temporary tables is temp, the command showed the names of schema and table of the temporary table such as temp.temp_table1. tables Code language: SQL (Structured Query Language) ( sql ) Second, list all tables from the database. See the following example:įirst, create a new temporary table named temp_table1: CREATE TEMPORARY TABLE temp_table1( name TEXT ) Code language: SQL (Structured Query Language) ( sql ) tables command also can be used to show temporary tables. tables command lists all tables in the chinook database albums employees invoices playlistsĬustomers invoice_items playlist_track Code language: SQL (Structured Query Language) ( sql ) tables command: tables Code language: SQL (Structured Query Language) ( sql ) For each table read by the query, the output of EXPLAIN QUERY PLAN includes a record for which the value in the 'detail' column begins with either 'SCAN' or 'SEARCH'. The above statement opened the database named chinook.db that locates in the c:\sqlite\db directory. The various ways in which SQLite may retrieve data from a table or index are described in detail here. To show tables in a database using the sqlite command-line shell program, you follow these steps:įirst, open the database that you want to show the tables: sqlite3 c:\sqlite\db\chinook.db Code language: SQL (Structured Query Language) ( sql ) Showing tables using the sqlite command line shell program Summary: in this tutorial, you will learn various ways to show tables from an SQLite database by using the SQLite command-line shell program or by querying data from sqlite_master tables.
0 Comments
Leave a Reply. |