Метаданные Информации Схемы SQLite

Question

Мне нужно получить имена столбцов и их таблицы в базе данных SQLite. Что мне нужно, это набор результатов с 2 столбцами: table_name | column_name,

В MySQL я могу получить эту информацию с помощью SQL-запроса к базе данных INFORMATION_SCHEMA, Однако SQLite предлагает таблицу 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)

который приводит запрос строения DDL (CREATE TABLE), что не полезно для меня, и мне нужно проанализировать это, чтобы получить соответствующую информацию.

Мне нужно получить список таблиц и объединить их со столбцами или просто получить столбцы вместе с именем столбца таблицы. Так PRAGMA table_info(TABLENAME) не работает для меня, так как у меня нет имени таблицы. Я хочу получить все метаданные столбца в базе данных.

Есть ли лучший способ получить эту информацию в результате набора путем запроса базы данных?

Answer 1

Вы в основном назвали решение в своем вопросе.

Чтобы получить список таблиц (и представлений), запросите sqlite_master как в

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

(см. FAQ по SQLite)

Чтобы получить информацию о столбцах в определенной таблице, используйте PRAGMA table_info(table-name); как объяснено в документации SQLite PRAGMA.

Я не знаю ни одного способа получить возвращаемое имя таблицы / столбца в результате одного запроса. Я не верю, что SQLite поддерживает это. Лучше всего, вероятно, использовать два метода вместе, чтобы вернуть искомую информацию - сначала получите список таблиц с помощью sqlite_master, затем выполните цикл по ним, чтобы получить их столбцы с помощью PRAGMA table_info().

Answer 2

Последние версии SQLite теперь позволяют выбирать результаты PRAGMA, что упрощает:

SELECT 
  m.name as table_name, 
  p.name as column_name
FROM 
  sqlite_master AS m
JOIN 
  pragma_table_info(m.name) AS p
ORDER BY 
  m.name, 
  p.cid

где p.cid содержит порядок столбцов оператора CREATE TABLE с нулевым индексом.

Дэвид Гарутт ответил на это здесь, но этот SQL должен выполняться быстрее, а столбцы упорядочены по схеме, а не по алфавиту.

Обратите внимание, что table_info также содержит

• type (тип данных, как integer или же text),
• notnull (1 если столбец имеет NOT NULL ограничение)
• dflt_value (NULL если нет значения по умолчанию)
• pk (1 если столбец является первичным ключом таблицы, иначе 0)

RTFM: https://www.sqlite.org/pragma.html

Answer 3

Существуют команды ".tables" и ".schema [table_name]", которые дают вид отдельной версии для результата, который вы получаете из "select * from sqlite_master;"

Существует также "прагма table_info([имя_таблицы]);" Команда для получения лучшего результата для анализа вместо запроса на конструкцию:

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

Надеюсь, это поможет в некоторой степени...

Answer 4

К вашему сведению, если вы используете.Net, вы можете использовать DbConnection.GetSchema метод для получения информации, которая обычно находится в INFORMATION_SCHEMA. Если у вас есть уровень абстракции, вы можете иметь один и тот же код для всех типов баз данных (учтите, что MySQL, похоже, включает первые 2 аргумента массива ограничений).

Answer 5

Другой полезный прием - сначала получить все имена таблиц у sqlite_master.

Затем для каждого запускается запрос "select * from t, где 1 = 0". Если вы проанализируете структуру полученного запроса - зависит от того, с какого языка / API вы его вызываете - вы получите богатую структуру, описывающую столбцы.

В питоне

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

Юрай

PS. Я имею привычку использовать "где 1 = 0", потому что синтаксис ограничения записей, кажется, варьируется от дБ до дБ. Кроме того, хорошая база данных оптимизирует это всегда ложное предложение.

Тот же эффект в SQLite достигается с помощью "limit 0".

Answer 6

Попробуйте этот синтаксический анализатор таблиц sqlite, я реализовал синтаксический анализатор таблиц sqlite для анализа определений таблиц в PHP.

Он возвращает полные определения (уникальный, первичный ключ, тип, точность, не ноль, ссылки, ограничения таблицы... и т. Д.)

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

Синтаксис соответствует синтаксису оператора создания таблицы sqlite: http://www.sqlite.org/lang_createtable.html

Answer 7

Это старый вопрос, но из-за того, сколько раз его просматривали, мы добавляем этот вопрос по той простой причине, что в большинстве ответов рассказывается, как найти имена ТАБЛИЦ в базе данных SQLite. ЧТО ВЫ ДЕЛАЕТЕ, КОГДА ИМЯ ТАБЛИЦЫ В базе данных нет? Это происходит с нашим приложением, потому что мы создаем ТАБЛИЦЫ программно. Таким образом, приведенный ниже код будет иметь дело с проблемой, когда ТАБЛИЦА НЕ находится в базе данных или не создается. Наслаждайтесь

    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();
    }
}