EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQLite Tutorial SQLite Describe Table

SQLite Describe Table

SQLite Describe Table

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

.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

SQLite Describe Table 1

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.

SQLite Describe Table 2

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.

SQLite Describe Table 3

.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 Describe Table 4

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.

SQLite Describe Table 5

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

We hope that this EDUCBA information on “SQLite Describe Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQLite Commands
  2. SQLite add column
  3. SQLite
  4. SQL COALESCE
SPSS Training Program
41+ Hours of HD Videos
14 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE
97+ Hours of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
HADOOP Training Program
170+ Hours of HD Videos
32 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
INFORMATICA Training
69+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQLite Tutorial
 12+ Hours of HD Videos
4 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more