SQLite Show Tables

SQLite is a lightweight database library written in C which is used for embedding the database with applications. SQLite is a serverless, lightweight, cross-platform, and highly reliable database engine that provides the standard SQL syntax making it easy to use standalone or integrate with any other programming language. SQLite is used to develop embedded applications and is widely used in small-scale and medium-scale databases.

In this article, we will understand how to show tables of databases using various methods and so on. After reading this article you will have decent knowledge about how to SHOW Tables in SQLite.

Show Tables Using .tables SQLite Command

.tables is the special SQLite command that is used to extract the list of tables from the database file. It is specific to the SQLite command line shells and doesn’t work in SQL scripts. Internally .tables executes the query over the SQLite_master table and lists out the tables present in the database file.

The below image shows How to see all the tables in a database using the .tables command:

showing tables using .table command

Steps to Follow:

1. Open the SQLite3 command line interface either on the command prompt or by directly running the .exe file.
2. To use the database in SQLite there is command called .open. So here we have used the student.db database which is store locally in the system. Make sure to have our database in the same folder from which we have started the command line interface of the .
3. Now simply execute .tables special command to get the list of tables in the student database.

Show Tables Using sqlite_master Table

sqlite_master table is the system table in SQLite that is used to store the metadata of the database. This table contains various data related to the database, such as tables, indices, views and more.

Below image shows how to see all tables in a database using sqlite_master table :

showing tables by querying sqlite_master table

Explanation:

In the above image we have performed the same steps for opening the database file as discussed above, but this time we have written a query to extract the tables from database file.

The output of query has shown many columns, let’s discuss about each column in brief

• type -> It shows the type of database object such as table, indices, views etc.
• name -> It shows the name of database object.
• tbl_name -> It shows the name of the table of which that database object is, In case of type=’table‘ name and tbl_name are same.
• rootpage -> In SQLite each table is organized as a B-Tree structure. So the rootpage value points to the page number of root B-Tree page of table in the database file.
• sql -> It shows the sql statement which was used to create that particular table.

Now in the above image we can clearly see that the query has given the three outputs for the query : select * from sqlite_master where type=’table’;

Here this query has given the 3 tables : student, sqlite_sequence, teacher

student and teacher tables are created by the user, but sqlite_sequence is created by the SQLite to know the maximum row id for the auto incrementing columns for all the tables in database file. This is required so that SQLite can add the row id to the new row with the help of this table.

Show Tables Using Programming Language

SQLite is not frequently used as a standalone application, instead, it integrates with different programming languages and we can use them to display tables.

For example, we can use the following program in C to display tables:

C

#include<stdio.h>
#include<string.h>
#include<sqlite3.h>

int rowHandler(void *ptr, int columnCount, char **dataPtr, char **columnNamePtr)
{
if(ptr != NULL)
{
printf("%s\n", (char*)ptr);
}
for(int x=0 ; x<columnCount; x++)
{
if(strcmp(columnNamePtr[x],"tbl_name")==0)
printf("%s : %s\n", columnNamePtr[x], dataPtr[x]);
}
return 0;
}

int main()
{
char *errorMessage = NULL;
sqlite3 *db;
char sql[1001];
int result = sqlite3_open("student.db",&db);
if(result != SQLITE_OK)
{
printf("Unable to connect %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 0;
}
result = sqlite3_exec(db,"select * from sqlite_master where type='table';",rowHandler,"Student",&errorMessage);
if(result != SQLITE_OK)
{
printf("Unable to retrieve data, result : %s\n", errorMessage);
sqlite3_free(errorMessage);
sqlite3_close(db);
return 0;
}
sqlite3_close(db);
return 0;
}

Output:

Explanation:

Let’s understand some basic concepts to understand the above program:

1. sqlite3.h: It is the header file which we get from the zip file we download.
   
2. sqlite3 *db: We have declared a pointer db of type sqlite3. Here sqlite3 is the structure which consists of the necessary functionalities to perform SQLite task using C.
3. sqlite3_open(“student.db”,&db): It is the function in sqlite3 library which is used to open the database. Internally it perform the .open operation in SQLite. It takes the two arguments first one is of const char* type which is used to take the name of database and second one is of type sqlite3 ** takes the address of the sqlite3 type pointer. Note that it takes the address of the sqlite3 type pointer not the address of sqlite3 type object.
4. SQLite_OK: It is a result code in C++ interface of SQLite which is means that if the result returns the value equals to SQLite_OK then the operation is successfully executed.
5. sqlite3_errmsg(db): If any error occurs then sqlite3_errmsg will return the error in the form of const char *. It takes the address of sqlite3 type object.
6. sqlite3_exec(db,”select * from sqlite_master where type=’table’;”,rowHandler,”Student”,&errorMessage): It is the main part of whole program. sqlite3_exec is the function which is used to execute the SQLite statements and queries on the table. Now it takes the 5 arguments:
   1. The first argument is of type sqlite3 * type which is takes the pointer of type sqlite3 type object. Here we have passed db as it has all the necessary information about student.db
   2. The second argument is the basic query we have learnt above in querying sqlite_master table section.
   3. The third argument is of type int(func *)(void *, int, char **, char **) which SQLite calls for each output of the select operation. The first parameter of the function is of void * type which is decided by the user of which type they want to send by passing the 4th argument of sqlite3_exec function. In this case we have passed the “Student” string which will be of char * type.
      You can pass the address of whichever object you want and then later typecast is from void to desired type inside the function. NULL can also be passed. Second parameter denotes the number of columns(columnCount), third parameter is a string array where each index of array has the columnName, fourth parameter is also a string array but here each index has the corresponding data string to a index in columnName.
   4. The fourth argument is of void * type as discussed above.
   5. The fifth and last argument of sqlite3_exec function is of type char ** which is used to give the error message if any otherwise will give NULL.
7. sqlite3_free(errorMessage) – It is used to free the memory allocated to a errorMessage. As we give the address of errorMessage to sqlite3_exec, so it can be freed by sqlite3_free function.
8. sqlite3_close(db) – It closes the database. Internally all the data structures and variables that are assigned will get freed by calling this function.

To execute the program we have to write the following command in the command prompt:

Query:

gcc file_name.c -o file_name.exe -I path_to_include_folder -L path_to_lib_folder -l library_name"

Flow of Program:

1. Declared a sqlite3 type pointer db.
2. Opened the student.db database and stored the necessary information in db by calling sqlite3_open function.
3. Checked if the operation is performed successfully or not. If not printed the error message and closed the database by calling sqlite3_close function.
4. After running the query using the sqlite3_exec function in SQLite, the database tends to invoke the rowHandler function repeatedly. The number of times the rowHandler function is called corresponds to the number of rows returned by the select query.
5. That’s why we can see in output for each table name Student is also getting printed again and again.
6. In the row handler function, we use the strcmp function to character array at the same index.
7. After successfully extracting we are just closing the database by calling sqlite3_close function.
8. If some problem occurs and sqlite3_exec gives some error then program will go in the if condition where we are just freeing the errorMessage pointer and closing the database.
9. Hence here is the brief explanation of how the program in C is working to show tables in the database.

Conclusion

Therefore, SQLite is one of the most important tools from the development perspective because it can be integrated with different programming languages. we have saw the different ways to display tables in like Using .table command, using sqlite_matser and using programming language, but the most popular and simplest way to display all tables in the database is using the .tables statement.