SQLite 테이블 구조(스키마) 확인 2가지 방법(desc tablename)

SQLite를 이용해서 테이블을 생성해서 이용하는 중에 테이블 구조를 확인해야 할 때가 있죠? Oracle이나 MySQL에서는 desc 명령어를 사용해서 확인하듯이, SQLite 테이블 구조 확인 방법을 함께 확인해 보겠습니다.

SQLite 스키마 확인용 테이블 생성

간단히 테스트할 테이블 먼저 만들어 보겠습니다.

CREATE TABLE users (
  id int,
  name text
);
CREATE TABLE subscribers (id int, name text, age int, subscription int) strict;

ShellScript

SQLite 테이블 스키마 확인 방법 2가지

.schema 명령어로 테이블 구조 확인하기

.schema 테이블명의 구조로 사용하면 됩니다. 저는 위에서 만든 users 테이블의 스키마를 확인해 보겠습니다. dot(.)으로 시작하는 명령어 사용시에는 명령줄 뒤에 세미콜론을 붙이지 않아야 합니다.

.schema users

SQL

위의 명령문을 사용하면 아래와 같은 결과가 나옵니다. 그런데 스키마 구조가 CREATE TABLE 문으로 나오죠? 맞습니다. SQLite는 스키마의 형태를 SQL 형태로 보관하고 관리합니다.

그림 1. .schema 명령어를 사용하여 SQLite 테이블 구조 확인

그리고, .schema에 파라미터를 붙이지 않으면 아래와 같이 모든 테이블의 스키마를 보여줍니다.

sqlite_schema Table 이용하여 테이블 구조 확인하기

이번에는 sqlite_schema 테이블을 확인해 보도록 하겠습니다. SQLite에서 관리하는 스키마 테이블은 sqlite_schema입니다. 이러한 sqlite_schema 테이블을 통해서 모든 테이블, 인덱스, 트리거와 뷰에 대한 정보를 확인할 수 있습니다. sqlite_schema는 또다른 대체 이름으로 sqlite_master라는 이름을 이용해서 사용 가능합니다. 그렇다면 sqlite_schema 테이블의 스키마를 먼저 확인해 보겠습니다.

CREATE TABLE sqlite_schema(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

SQL

위와 같은 구조로 생겼습니다.

sqlite_schema 테이블 구조

type 컬럼은 개체 유형에 따라 ‘table’, ‘index’, ‘view’ 또는 ‘trigger’ 텍스트 문자열 중 하나의 값을 갖습니다.

name 컬럼은 객체의 이름을 갖습니다.

tbl_name 컬럼은 type이 table이나 view인 경우에 name 컬럼의 복사본으로서 name과 동일한 값을 갖습니다. type이 index인 경우에는 인덱싱된 테이블의 이름이 됩니다. type이 trigger인 경우에는 트리거를 발생시키는 테이블이나 뷰의 이름이 저장됩니다.

rootpage 컬럼에는 테이블과 인덱스에 대한 root b-tree 페이지의 페이지 번호가 저장됩니다. view, trigger 및 가상 테이블의 경우에는 0이나 NULL 값을 갖습니다.

sql 컬럼에는 개체를 설명하는 SQL이 텍스트의 형태로 저장됩니다.

그러면 sqlite_schema 테이블의 구조도 살펴봤으니, 이제 SELECT 문을 사용해서 users 테이블의 구조를 확인해 보도록 하겠습니다.

SELECT * FROM sqlite_schema WHERE name = 'users';

SQL

일단 *을 사용하여 모든 컬럼을 보이도록 했습니다. 제일 마지막 sql 컬럼 내용으로 테이블 생성 Query가 들어가 있습니다.

그림 2. SELECT문을 사용하여 sqlite_schema 테이블을 통해 SQLite 테이블 스키마 확인 — 그림 3. SELECT문을 사용하여 sqlite_schema 테이블을 통해 SQLite 테이블 스키마 확인

.mode 활용 방법

.mode table

그런데 각 컬럼의 이름을 보여주지 않습니다. 이럴 때에는 .mode table 이라고 해 주면, MySQL에서 select 쿼리를 날렸을 때 처럼 아래와 같이 보여줍니다.

.mode table

SQL

.mode line

하지만 SQL문을 보기에는 다소 불편함이 있습니다. 그럴 때에는 .mode line 이라고 해 주시면 라인 별로 각 컬럼의 이름과 해당하는 값을 보여줍니다. 조금 더 깔끔하게 내용을 확인할 수 있네요.

.mode line

SQL

그 밖의 .mode 사용법

.mode의 사용법이 궁금하신 분들은 아래 명령어로 확인해 보세요.

.help mode

SQL

다음과 같이 다양한 옵션을 확인할 수 있습니다. 원하는 옵션을 선택해서 사용하시면 되겠습니다.

마무리

MySQL이나 Oracle의 경우에는 해당 구조 자체를 테이블 형식으로 보여주는데 반해, SQLite는 SQL 형식으로 보여준다는 점이 특이했습니다. ALTER TABLE을 사용해서 테이블을 변경하면 바로 저 SQL문이 변경되는 것을 알 수 있습니다.

여기까지 SQLite 테이블의 스키마(구조)를 확인하는 방법을 알아보았습니다. SQLite의 테이블 구조를 확인하고 싶으셨던 분들께 도움이 되셨길 바랍니다.