SQL command to extract schema of a SQLite table?

Question

I can't decide if this is a dumb question or not but I'll take the risk. :)

I'm a fairly simple SQL user - I understand CREATE/DROP tables and SELECT, INSERT, DELETE in their basic forms but not much more. What I'd like to know is if there's a way with SQL to request the schema of a table?

I have a PC app which uses SQLite and I'm writing a mobile client for it. I don't need all of the tables from the PC DB and not all columns from the tables. In SQLite Spy, I can right-click a table and select 'Show CREATE statement' which generates the SQL needed to recreate the table.

What I'd like to do (using code on the PC) to get the CREATE statement (or at least the schema) of the SQLite tables I need and then edit the strings to include only the columns I need. These could then be passed to my mobile app so it could create the 'subset' DB on the device. Is it possible?

EDIT: For anyone who comes after me looking for a similar answer. Thanks to dan04 pointing me in the right direction. The results of using...

SELECT * FROM sqlite_master

... include a column called 'sql' which includes the CREATE statement. Just what I'm looking for. :)

dan04 · Accepted Answer · 2011-01-18 23:48:38Z

11

It's stored in the sqlite_master table.

answered Jan 18, 2011 at 23:48

dan04

92k23 gold badges169 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Squonk Over a year ago

OK, thanks - is it normal for that table to be hidden? I can't see it with SQLite Spy (or the version I'm using anyway).

Squonk Over a year ago

Ignore the above, I guess it always is hidden. Worked out what I need to do thanks.

Collectives™ on Stack Overflow

SQL command to extract schema of a SQLite table?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related