CODEWITHSUNDEEP
sqlite
sovanesyan
sovanesyan

Reputation: 1108

List of system tables in SQLite

I am trying to filter all the tables in a SQLite database based on if they are system tables or user generated ones.

So far I've found out that they are the ones with the sqlite_ prefix and AllDataTypes.

Has anyone done something like this? Is there a list of them?

Thanks in advance.

Upvotes: 14

Views: 22664

Answers (3)

Tanmay Patil
Tanmay Patil

Reputation: 699

sqlite_autoindex_TABLE_N - which will have information UNIQUE and PRIMARY KEY constraints on ordinary table.

sqlite_statN - which will have where N is an integer. Such tables store database statistics gathered by the ANALYZE command and used by the query planner to help determine the best algorithm to use for each query.

Source : https://www.sqlite.org/fileformat2.html

sqlite_user - this table will be present ,if we set up authentication-required database.

Source : http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt

Upvotes: 1

sergio
sergio

Reputation: 101

I think it can be filtered by name (as you already done)

I've used script

SELECT 
  name, type
FROM 
  sqlite_master
WHERE 
  type in ('table', 'view')
AND 
  name not like 'sqlite?_%' escape '?'

Upvotes: 10

Sky Sanders
Sky Sanders

Reputation: 37104

There is only one system table that is of any consequence.

select * from sqlite_master

but you may get some useful information from sqlite_sequence,

Upvotes: 20

Related Questions