Explore

SQL to report on all the tables in a database, and all the fields in those tables

To report on all the tables in a SQL database and all the fields in those tables, you can query the `sqlite_master` table to get the list of tables and then use the `PRAGMA table_info` command to get the details of each table's fields.

Here's the SQL code to achieve this:

1. **Query to List All Tables:** SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%';

2. **Query to Get Columns for Each Table:** For each table obtained from the above query, you can run the following command to get the columns: PRAGMA table_info(table_name);

### Combined Example: Below is an example of how you can combine these queries in a script to list all tables and their fields:

```sql -- Get the list of all tables SELECT name INTO TEMPORARY TABLE temp_tables FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%';

-- Initialize a cursor to iterate through each table DECLARE table_cursor CURSOR FOR SELECT name FROM temp_tables;

-- Declare a variable to hold table names DECLARE @table_name VARCHAR(255);

-- Open the cursor OPEN table_cursor;

-- Fetch the first table name FETCH NEXT FROM table_cursor INTO @table_name;

-- Loop through all table names WHILE @@FETCH_STATUS = 0 BEGIN -- Print table name PRINT 'Table: ' + @table_name;

-- Get columns for the current table PRAGMA table_info(@table_name);

-- Fetch the next table name FETCH NEXT FROM table_cursor INTO @table_name; END;

-- Close and deallocate the cursor CLOSE table_cursor; DEALLOCATE table_cursor;

-- Drop the temporary table DROP TABLE temp_tables; ```

### Explanation: 1. **Get the list of all tables:** ```sql SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'; ```

2. **Get columns for each table:** For each table, you can use: ```sql PRAGMA table_info(table_name); ```

Since SQLite does not support procedural code like cursors directly in SQL, you can create a script in a language like Python to iterate through the tables and fetch their columns. Here is an example using Python:

### Python Example:

```python import sqlite3

# Connect to SQLite database conn = sqlite3.connect('your_database.sqlite') cursor = conn.cursor()

# Get the list of all tables cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';") tables = cursor.fetchall()

# Loop through all tables and get columns for table in tables: table_name = table[0] print(f'Table: {table_name}') # Get columns for the current table cursor.execute(f"PRAGMA table_info({table_name});") columns = cursor.fetchall() for column in columns: print(f" Column: {column[1]}, Type: {column[2]}, Not Null: {column[3]}, Default: {column[4]}, Primary Key: {column[5]}") print()

# Close the connection conn.close() ```

### Explanation: 1. **Connect to SQLite database**: Establish a connection to your SQLite database. 2. **Get the list of all tables**: Execute a query to get all table names. 3. **Loop through all tables**: Iterate through the tables and execute `PRAGMA table_info` to get column details. 4. **Print details**: Print the table and column details. 5. **Close the connection**: Close the database connection.

This script will print all tables and their columns, including the column name, type, not null constraint, default value, and whether it is a primary key.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.