# find table name and comumns definintion for SQL and variences

<div class="flex flex-grow flex-col gap-3" id="bkmrk-standard-sql%3A-for-da"><div class="min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4"><div class="empty:hidden">  
</div><div class="empty:hidden">**Standard SQL:**</div><div class="empty:hidden">For databases that support the ANSI SQL standard and have the `INFORMATION_SCHEMA` views available, you can use the following query:</div></div></div>```sql
SELECT table_name, column_name
FROM information_schema.columns;
```

<div class="flex flex-grow flex-col gap-3" id="bkmrk-you-can-append-if-yo"><div class="min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4"><div class="empty:hidden">you can append if you want to specify where  
</div></div></div>```sql
WHERE table_schema = 'your_database_name'; 
```

<div class="flex flex-grow flex-col gap-3" id="bkmrk-if-you-want-database"><div class="min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4"><div class="empty:hidden">if you want **Database-specific Queries:** If you are working with a specific database system and the standard SQL approach doesn't work, you can try the following methods:</div></div></div>**MySQL/MariaDB:**

```mysql
SELECT table_name, column_name
FROM information_schema.columns;
```

<div class="flex flex-grow flex-col gap-3" id="bkmrk-or"><div class="min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4"><div class="empty:hidden">**or**</div></div></div>```mysql
SHOW TABLES;
DESCRIBE table_name;
```

<div class="flex flex-grow flex-col gap-3" id="bkmrk-postgresql%3A"><div class="min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4"><div class="empty:hidden">**PostgreSQL:**</div></div></div>```sql
 SELECT table_name, column_name
FROM information_schema.columns;
```

<div class="flex flex-grow flex-col gap-3" id="bkmrk-sqlite%3A"><div class="min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4"><div class="empty:hidden">**SQLite:**</div><div class="empty:hidden"><div class="empty:hidden">  
</div><div class="empty:hidden">  
</div></div></div></div>```sqlite
SELECT name AS table_name, sql AS column_definition
FROM sqlite_master
WHERE type = 'table';
```

<div class="flex flex-grow flex-col gap-3" id="bkmrk-you-would-run-this-s"><div class="min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4"><div class="empty:hidden"><div class="empty:hidden">  
</div></div><div class="empty:hidden">  
</div><div class="empty:hidden">You would run this SQLite command when you want to list all the tables in your SQLite database along with their SQL schema.</div></div></div>SQLite keeps a system table, `sqlite_master`, where it stores metadata about the database. Each row of `sqlite_master` represents an object (table, index, etc.) in the database.

The columns are:

<div class="flex flex-grow flex-col gap-3" id="bkmrk-type%3A-the-type-of-th"><div class="min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4"><div class="markdown prose w-full break-words dark:prose-invert light">- type: the type of the database object, such as 'table' or 'index'.
- name: the name of the object.
- tbl\_name: the name of the table to which the object is associated. For a table, it's the same as `name`.
- rootpage: the page number in the database file where the root B-tree page for the object is stored.
- sql: the SQL statement that created the object.

</div></div></div>This command specifies `type = 'table'` in the `WHERE` clause, so it only selects tables, not other types of objects like indices. For each table, it selects the name (renamed as `table_name` for clarity) and the SQL statement that created the table (as `column_definition`).

So this command is useful when you need to know the structure of all tables in your SQLite database, such as the table names and their corresponding column definitions. It's a handy tool for exploring a database when you don't have the schema in front of you or when you've inherited a database and need to understand its structure.