SQLite Show the Structure of a Table

“SQLite provides the user with various tools like the describe command, which allows us to inspect the table’s precise structure. In the SQLite database, the .schema query is used to define the tables; it is a control program, and we must follow specific procedures if we need an effect of the table at that moment. Essentially, describing the table implies printing information about each column, such as the column name, data type, and size. There are numerous methods in SQLite for retrieving data from a table.”

How to Display the Structure of a Table

Let’s have a glance at the description table command in action. In SQLite, the schema command operates as follows. In most cases, the table can be described in four ways. We have shown the syntax of each method through which we can view the structure of the table.

Syntax

# PRAGMA table_info(table name)
This command returns a single row from each column in the defined table, similar to the SQL DESCRIBE command.

# PRAGMA table_xinfo(table name)
This method is similar to the previous statement. The only distinction is that it provides the virtual table’s hidden column.

 # .schema table name
This operation represents the selected table; however, we must first build the table before using it.

# Sqlite_master Table
Another method of showing the table’s structure is by using the SQLite master table is this one. Each SQLite database’s schema is kept in a unique “schema table.” The schema is a visual representation of all the database’s records, collections, triggers, and views.

SQLite Table Creation for Performing the Method That Shows the Structure of the Table

Let’s peek at several alternative ways of describing tables. To begin, we’ll use the create table command to construct a table.

We have used below the create command for creating a table and defined a table name as “pet.” We have specified some fields with the data types inside the pet table. The pet table fields include pet_id, pet_name, pet_price, pet_gender, and pet_stock with the different data types as per their requirement.

Then, we have added some values against each field of the table pet by using the SQLite insert command. You can add the values with the insert command at once, but we have inserted them one by one.

Example 1: Using PRAGMA table_info() Method to Show the Table’s Structure

The PRAGMA table info() function returns a single entry on every field in the table given. It’s similar to MySQL’s DESCRIBE statement in SQLite. We have used a PRAGMA table_info() command here and passed a table name “pet” to it.

When we execute the above-mentioned command, the SQLite shell represents the pet table structure like in the figure. Here, it shows the field name and the data type which we have assigned to them explicitly. Against each field, we can see two more columns. The first column shows the not null values, and the second column displays the primary key value as the pet_id is the primary key of the table pet, so there is a 1 in the primary key column.

Example 2: Using PRAGMA table_xinfo() Method to Show the Table’s Structure

The PRAGMA table xinfo() statement is identical to the PRAGMA table info() function, except that it provides hidden fields on virtual tables as well. This is the option to utilize if you wish to inspect a virtual table’s structure, including any concealed columns. We have passed a table name pet to the following command.

We can see the results of the PRAGMA table_info(pet) command, as the fourth column is of the hidden table, but we haven’t hidden any column in the table pet, so all the values are zeros, and it’s not a virtual table.

If we want to see the table structure vertically, then we have to give the .mode line command in the MongoDB shell. Then, specify the command “PRAGMA table_xinfo()” in the shell.

Results of using .mode line showing the structure of table pet vertically.

Example 3: Using the .schema Command Method to Show the Table’s Structure

The schema command is another approach for getting the structure of a table. This is one of the several techniques to get the SQL that was used to construct the table. Here’s another example that uses the same table as the last one.

We have given a command .schema with the table name pet, which shows the structure of the table we have created above in the following cases.

As you can see, the schema of the table pet is the same as the time of creation and setting of the table pet field. As seen in the following diagram, the .schema command produces the result.

Example 4: Using the sqlite_master Table Method to Show the Table’s Structure

All SQLite databases have a table called SQLite master. This table’s content explains the schema of the database. As in the previous example, we can perform the same approach with the SQLite master table. Well, here’s an example, each time utilizing the same table.

We have included a select command from the sqlite_master table as shown. Then we have a WHERE clause to which we have specified a table name pet to an object tbl_name.

The output of the overhead query is demonstrated in the next snapshot.

Conclusion

We hope that after understanding this artifact, you have a better understanding of how SQLite represents table structures. We learned the different syntax of SQLite for viewing the table and saw several examples of SQLite describing the table structure in the recent article. We also learned how to describe tables using SQLite rules. We learned how to utilize the SQLite, describe the table in this post, and when to use it.