Definition of SQLite Describe Table
SQLite provides the different commands to the user such as the describe command, in which we can see the detailed structure of the table. SQLite database uses the .schema command to describe the table; the .schema is a command-line program and if we need to get a detailed structure of the table at that time we need to follow some steps. Basically describing the table means we can print details of each and every column such as column name, data type of that column, and size of that column. SQLite provides the four different ways to describe the table.
Syntax:
.schema specified table name
Explanation
In the above syntax, we use .schema command to describe the table structure and the specified table name means the actual table name that we need to describe.
How Describe Table works in SQLite?
Now let’s see the how describe table command that is .schema command works in SQLite as follows. Normally there are four different ways to describe the table as follows.
.schema
This command is used to describe the specified table; in this command first, we need to create the table.
PRAGMA table_info()
This command is equivalent to the SQL describe command, this command returns the single row from every column from the specified table.
PRAGMA table_xinfo()
This statement is the same as the above statement. The only difference is that it also returns the hidden column that specified the virtual table.
Sqlite_master Table
This is one of the commands to describe the table that is sqlite_master.
Each SQLite database has a solitary “schema table” that holds the schema for that database. Database uses the schema and is a depiction of the entirety of different tables, lists, triggers, and views that are contained inside the database.
SQLite database has alternative names
sqlite_schema is basically used as a reference for the schema table and it is granted by the name is main.sqlite_schema. As well as it has some alternative names as follows.
sqlite_master
sqlite_temp_schema
sqlite_temp_master
The above-mentioned second and third alternatives are only used for the temp database and are associated with each and every database connection.
Interpretation of the Schema Table
Schema uses the different parameters as follows.
type
SQLite type means text string such as table, view, index, or trigger as that depends on which type of object we defined. The type we can represent as sqlite_schema.type.
name
Name means column store name of object. In this parameter, we can also define the unique and primary key constraint at that time of table creation and it created the internal index with name. Without a rowid table, we can’t use the primary key constraint that means sqlite_schema does not allow for the primary key bur SQLite auto index uses the primary key.
tbl_name
This parameter is used to store the table name or a view. We can represent tbl_name as sqlite_schema_tbl_name.
root page
It is used to store the page number of root b tree page for the table and index. We can represent rootpage as sqlite_schema_rootpage.
SQL
sql provides the different command means we can create tables, create views, create virtual tables or we can say create triggers that are executed against the database. We represent SQL as sqlite_schema.sql.
Examples
Now let’s see the different examples of describing tables as follows. First, we need to create a table by using the create table statement as follows.
create table company_emp (emp_id integer primary key, emp_name text not null, emp_dept text not null, emp_salary text not null);
Explanation
In the above example, we use the use create table statement to create a new table name as company_emp with different fields such as emp_id, emp_name, emp_dept and emp_salary with different data types as shown in the above statement. The end output of the above statement we illustrate by using the following screenshot as follows.
.table
Now we can describe the table by using the following command as follows.
PRAGMA table_info:
Syntax
PRAGMA schema.table_info(specified table name);
Explanation
In the above syntax, we use PRAGMA command with schema. table and specified table name parameter, here specified table means actual table name, schema is an optional part of this syntax.
Example
PRAGMA table_info(company_emp);
Explanation
By using the above statement we describe the company_emp table, here the first column represents the column id, second is used for the column name, after that third used for the column name, next column is used for not null constraint and it represents by using 0 and 1, 1 for not null constraint and 0 for null constraint. The last field is used for the primary key and that also represented by using the 0 and 1, 1 for a primary key, and 0 for a non-primary key column. The end output of the above statement we illustrate by using the following screenshot as follows.
PRAGMA table_xinfo:
This command is used to show the hidden column from the virtual table.
Syntax:
PRAGMA table_xinfo(specified table name);
Explanation:
This statement and the above statement is the same, but the only difference is that here we describe the hidden column.
Example
PRAGMA table_xinfo(company_emp);
Explanation
The working of this statement is the same as the above-mentioned statement as well as the field is also the same, here they just add hidden columns at the end of the table and that represent by using 0 and 1, 1 for hidden columns and 0 for non-hidden columns. The end output of the above statement we illustrate by using the following screenshot as follows.
.schema command:
Example
.schema company_emp
Explanation
In the above example, we use the .schema command with a table name. The end output of the above statement we illustrate by using the following screenshot as follows.
sqlite_master table:
Example
SELECT sql
FROM sqlite_master
WHERE tbl_name = 'company_emp';
Explanation
In the above example, we use a select statement with sql_master. The end output of the above statement we illustrate by using the following screenshot as follows.
Conclusion
We hope from this article you have understood about the SQLite describe the table. From the above article, we have learned the basic syntax of SQLite describe table and we also see different examples of SQLite describe table. We also learned the rules of SQLite describe tables. From this article, we learned how and when we use the SQLite describe table.
Recommended Articles
This is a guide to SQLite Describe Table. Here we discuss definition, syntax, and How describe table works in SQLite? with examples. You may also have a look at the following articles to learn more –
