I am trying to count the columns from a sqlite db using the sqlite command line tool. To test it I created a sample db like this:
c:\>sqlite.exe mydb.sqlite "create table tbl1(one varchar(10), two smallint);"
Now lets say i don't know that the table tbl1 has 2 columns, how can I find that using a query from the command line tool?
Run:
pragma table_info(yourTableName)
See:
http://www.sqlite.org/pragma.html#pragma_table_info
for more details.
Here is a way I found useful under Linux. Create a bash script file columns.sh and ensure it has execute permissions and copy - paste the following code.
columns() { for table in $(echo ".tables" | sqlite3 $1); do echo "$table $(echo "PRAGMA table_info($table);" | sqlite3 $1 | wc -l)"; done ;}
Type the following command, in terminal, on the first line to return results
$ columns <database name>
<table1> <# of columns>
<table2> <# of columns>
Note: Ensure database is not corrupted or encrypted.
UPDATE
Here is an interesting URL for Python Script Solution
Change your query to:
SELECT COUNT(*) FROM pragma_table_info(tablename);
