Metadatos de información de esquema SQLite

information-schema

Question

Necesito obtener los nombres de las columnas y sus tablas en una base de datos SQLite. Lo que necesito es un conjunto de resultados con 2 columnas: table_name | column_name table_name | column_name .

En MySQL, puedo obtener esta información con una consulta SQL en la base de datos INFORMATION_SCHEMA . Sin embargo, el SQLite ofrece la tabla sqlite_master :

sqlite> create table students (id INTEGER, name TEXT);
sqlite> select * from sqlite_master;
  table|students|students|2|CREATE TABLE students (id INTEGER, name TEXT)

que resulta una consulta de construcción DDL ( CREATE TABLE ) que no es útil para mí y necesito analizar esto para obtener información relevante.

Necesito obtener una lista de tablas y unirlas con columnas o simplemente obtener columnas junto con la columna del nombre de la tabla. Entonces PRAGMA table_info(TABLENAME) no me funciona porque no tengo el nombre de la tabla. Quiero obtener todos los metadatos de columna en la base de datos.

¿Hay alguna forma mejor de obtener esa información como resultado de consultar la base de datos?

Answer 1

Básicamente has nombrado la solución en tu pregunta.

Para obtener una lista de tablas (y vistas), consulta sqlite_master como en

SELECT name, sql FROM sqlite_master
WHERE type=''table''
ORDER BY name;

(ver las preguntas frecuentes de SQLite )

Para obtener información sobre las columnas en una tabla específica, use PRAGMA table_info(table-name); como se explica en la documentación de SQLite PRAGMA .

No sé de ninguna forma para obtener tablename | columnname devuelto como resultado de una sola consulta. No creo que SQLite apoye esto. Probablemente su mejor opción sea utilizar los dos métodos juntos para devolver la información que está buscando: primero obtenga la lista de tablas usando sqlite_master, luego repásalos para obtener sus columnas usando PRAGMA table_info ().

Answer 2

Esta es una pregunta antigua, pero debido al número de veces que se ha visto, agregamos a la pregunta por la simple razón de que la mayoría de las respuestas le dicen cómo encontrar los nombres TABLE en la base de datos SQLite ¿QUÉ HACE CUANDO EL NOMBRE DE LA TABLA? NO ESTÁ EN LA BASE DE DATOS? Esto está sucediendo en nuestra aplicación porque estamos creando TABLAS programáticamente. Por lo tanto, el siguiente código tratará el problema cuando la TABLE NO esté en la base de datos o no esté creada por ella.

    public void toPageTwo(View view){

    if(etQuizTable.getText().toString().equals("")){
        Toast.makeText(getApplicationContext(), "Enter Table Name/n/n"
                +"           OR"+"/n/nMake Table First", Toast.LENGTH_LONG 
   ).show();
        etQuizTable.requestFocus();
        return;
    }

    NEW_TABLE = etQuizTable.getText().toString().trim();
    db = dbHelper.getWritableDatabase();
    ArrayList<String> arrTblNames = new ArrayList<>();
    Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE 
   type=''table''", null);

    if (c.moveToFirst()) {
        while ( !c.isAfterLast() ) {
            arrTblNames.add( c.getString( c.getColumnIndex("name")) );
            c.moveToNext();
        }
    }
    c.close();
    db.close();

    boolean matchFound = false;
    for(int i=0;i<arrTblNames.size();i++) {
        if(arrTblNames.get(i).equals(NEW_TABLE)) {
            Intent intent = new Intent(ManageTables.this, TableCreate.class 
   );
            startActivity( intent );
            matchFound = true;
        }
    }
    if (!matchFound) {
        Toast.makeText(getApplicationContext(), "No Such Table/n/n"
                +"           OR"+"/n/nMake Table First", Toast.LENGTH_LONG 
 ).show();
        etQuizTable.requestFocus();
    }
}

Answer 3

FYI, si usa .Net, puede usar el método DbConnection.GetSchema para recuperar información que normalmente se encuentra en INFORMATION_SCHEMA. Si tiene una capa de abstracción, puede tener el mismo código para todos los tipos de bases de datos (tenga en cuenta que MySQL parece esquivar los primeros argumentos de la matriz de restricciones).

Answer 4

Hay comandos ".tables" y ".chema [table_name]" que dan una especie de versión separada del resultado que obtienes de "select * from sqlite_master;"

También hay "pragma table_info ([table_name]);" comando para obtener un mejor resultado para el análisis en lugar de una consulta de construcción:

sqlite> .tables
students
sqlite> .schema students
create table students(id INTEGER, name TEXT);
sqlite> pragma table_info(students);
0|id|INTEGER|0||0
1|name|TEXT|0||0

Espero, ayuda en cierta medida ...

Answer 5

Otro truco útil es obtener primero todos los nombres de tablas de sqlite_master.

Luego, para cada uno, ejecute una consulta "select * from t where 1 = 0". Si analiza la estructura de la consulta resultante, depende del idioma / api desde la que la llama, obtiene una estructura detallada que describe las columnas.

En python

c = ...db.cursor()
c.execute("select * from t where 1=0");
c.fetchall();
print c.description;

Juraj

PD. Tengo el hábito de usar ''donde 1 = 0'' porque la sintaxis de limitación de registros parece variar de db a db. Además, una buena base de datos optimizará esta cláusula siempre falsa.

El mismo efecto, en SQLite, se logra con ''límite 0''.

Answer 6

Pruebe este analizador de esquema de tabla sqlite. Implementé el analizador de tablas sqlite para analizar las definiciones de tabla en PHP.

Devuelve las definiciones completas (clave única, principal, tipo, precisión, no nulo, referencias, restricciones de tabla ... etc.)

https://github.com/maghead/sqlite-parser

La sintaxis sigue a sqlite create sintaxis de declaración de tabla: http://www.sqlite.org/lang_createtable.html