How do you create an index on the tables themselves in a database?

Question

I'm using APSW to wrap my SQL code in Python, like so:

connection=apsw.Connection("dbfile"); cursor=connection.cursor()
cursor.execute("create table foo(x,y,z)")
cursor.execute("create table bar(a,b,c)")
cursor.execute("create table baz(one,two,three)")

I plan on using a GUI framework to display these table names (and subsequently their columns and rows). I would also like to sort these table names by different criteria.

Is there a way to create an index on the tables themselves to be used for sorting – e.g.

cursor.execute("CREATE INDEX index_name ON foo")

Many databases include metadata of the nature you're already looking for in their schema catalogs. Knowing which exact database you're trying to use might help us guide you to the documentation for those catalogs; Alternatively, if you want a solution that works across databases, please include that in your question as well. — SingleNegationElimination, Sep 08 '13 at 18:54
@TokenMacGuy: You're right. From what i could gather, i'd be using the "SQLite 3 database library". Does that help, or did you have something more specific in mind? — Noob Saibot, Sep 08 '13 at 19:05

unutbu · Accepted Answer · 2013-09-08T21:22:34.057

1

You can list all the tables in the sqlite database with

cursor.execute(
    "SELECT tbl_name FROM sqlite_master WHERE type='table'")

table_names = cursor.fetchall()

Once you have the table names, you can use string formatting to form the CREATE INDEX commands.

edited Sep 08 '13 at 21:22

answered Sep 08 '13 at 19:04

unutbu

842,883
184
1,785
1,677

Thanks! I didn't even know `SQLITE_MASTER` existed. But a followup question: Is `rootpage` the index i was looking for, or is that something i shouldn't be changing willy-nilly? – Noob Saibot Sep 08 '13 at 19:15
[The `rootpage`](http://books.google.com/books?id=WLinoJaOUCwC&pg=PA304&lpg=PA304&dq=sqlite+rootpage&source=bl&ots=8AWgqgvNRv&sig=IvuQ0byqMBiVxMvP9lRhRFiUkpE&hl=en&sa=X&ei=xd4sUsj2CpfJ4AP474GwDw&ved=0CCgQ6AEwADgK#v=onepage&q=sqlite%20rootpage&f=false) is the first B-tree page of the object in the database. Don't change it :) – unutbu Sep 08 '13 at 20:36

score 0 · Answer 2 · answered Sep 09 '13 at 00:56

0

In addition to @unutbu answer, you also have the PRAGMA table_info() function:

PRAGMA table_info(table-name);

It returns information on individual tables.

answered Sep 09 '13 at 00:56

Alix Axel

151,645
95
393
500

How do you create an index on the tables themselves in a database?

2 Answers2