How can I get the list of a columns in a table for a SQLite database?

reflection sqlite list
AngryHacker picture AngryHacker · Mar 3, 2009 · Viewed 96.8k times · Source

I am looking to retrieve a list of columns in a table. The database is the latest release of SQLite (3.6, I believe). I am looking for code that does this with a SQL query. Extra bonus points for metadata related to the columns (e.g. length, data type, etc...)

Answer

Bryan Kyle picture Bryan Kyle · Mar 3, 2009

What you're looking for is called the data dictionary. In sqlite a list of all tables can be found by querying sqlite_master table (or view?)

sqlite> create table people (first_name varchar, last_name varchar, email_address varchar);
sqlite> select * from sqlite_master;
table|people|people|2|CREATE TABLE people (first_name varchar, last_name varchar, email_address varchar)

To get column information you can use the pragma table_info(table_name) statement:

sqlite> pragma table_info(people);
0|first_name|varchar|0||0
1|last_name|varchar|0||0
2|email_address|varchar|0||0

For more information on the pragma statements, see the documentation.

Related questions

What could cause java.lang.reflect.InvocationTargetException?

Well, I've tried to understand and read what could cause it but I just can't get it: I have this somewhere in my code: try{ .. m.invoke(testObject); .. } catch(AssertionError e){ ... } catch(Exception e){ .. } Thing is that, when it tries …

Read More
How do I invoke a Java method when given the method name as a string?

If I have two variables: Object obj; String methodName = "getName"; Without knowing the class of obj, how can I call the method identified by methodName on it? The method being called has no parameters, and a String return value. It's …

Read More
What is reflection and why is it useful?

What is reflection, and why is it useful? I'm particularly interested in Java, but I assume the principles are the same in any language.

Read More