Agent skill

dbcli-tables

List all tables in a database and show table structure (columns, types, constraints) for 30+ databases using DbCli. Use when user wants to explore database schema, see what tables exist, check table structure, or understand column definitions.

View SKILL.md on GitHub Repository
Stars 7
Forks 0

Install this agent skill to your Project

npx add-skill https://github.com/tteamtm/dbcli/tree/main/skills/dbcli-tables

Metadata

Additional technical details for this skill

tool
dbcli
version
1.0.0
category
database
operation type
read-only
supported databases
30+

SKILL.md

Command Style (Use PATH)

All examples use the plain command name dbcli (no directory prefix). Ensure dbcli is on PATH instead of hardcoding paths like .\.claude\skills\dbcli\dbcli.exe.

DbCli Tables Skill

List all tables and view table structures in databases.

When to Use This Skill

  • User wants to see all tables in a database
  • User needs to check table structure or schema
  • User asks "what tables exist" or "show me the database schema"
  • User wants to see column names, types, or constraints
  • User needs to explore an unfamiliar database

Command Syntax

List All Tables

bash
# Set environment variables first:
# export DBCLI_CONNECTION="connection-string"
# export DBCLI_DBTYPE="database-type"

dbcli [-f FORMAT] tables

Show Table Structure

bash
dbcli [-f FORMAT] columns TABLE_NAME

Global Options

  • Environment variables:
    • DBCLI_CONNECTION: Database connection string
    • DBCLI_DBTYPE: Database type (alternative to -t)
  • -t, --db-type: Database type (default: sqlite)
  • -f, --format: Output format: json (default), table, csv

List All Tables

Basic Usage

bash
# SQLite - JSON format (default)
export DBCLI_CONNECTION="Data Source=app.db"
dbcli tables

# Output: [{"TableName":"Users"},{"TableName":"Orders"},{"TableName":"Products"}]

# Table format (human-readable)
dbcli -f table tables

# Output:
# +-----------+
# | TableName |
# +-----------+
# | Users     |
# | Orders    |
# | Products  |
# +-----------+

Different Databases

bash
# SQL Server
export DBCLI_CONNECTION="Server=localhost;Database=mydb;Trusted_Connection=True"
export DBCLI_DBTYPE="sqlserver"
dbcli -f table tables

# MySQL
export DBCLI_CONNECTION="Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx"
export DBCLI_DBTYPE="mysql"
dbcli -f table tables

# PostgreSQL
export DBCLI_CONNECTION="Host=localhost;Database=mydb;Username=postgres;Password=xxxxxxxxxx"
export DBCLI_DBTYPE="postgresql"
dbcli -f table tables

# Oracle
export DBCLI_CONNECTION="Data Source=localhost:1521/orcl;User Id=system;Password=xxxxxxxxxx"
export DBCLI_DBTYPE="oracle"
dbcli -f table tables

# MongoDB
export DBCLI_CONNECTION="mongodb://localhost:27017/mydb"
export DBCLI_DBTYPE="mongodb"
dbcli -f table tables
bash
# DaMeng
export DBCLI_CONNECTION="Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb"
export DBCLI_DBTYPE="dm"
dbcli -f table tables

# KingbaseES
export DBCLI_CONNECTION="Server=localhost;Port=54321;UID=system;PWD=xxxxxxxxxx;database=mydb"
export DBCLI_DBTYPE="kdbndp"
dbcli -f table tables

# GaussDB
export DBCLI_CONNECTION="Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx"
export DBCLI_DBTYPE="gaussdb"
dbcli -f table tables

Show Table Structure

Basic Column Information

bash
# SQLite - Show Users table structure
export DBCLI_CONNECTION="Data Source=app.db"
dbcli -f table columns Users

# Output:
# +------------+----------+--------+------------+--------------+--------------+
# | ColumnName | DataType | Length | IsNullable | IsPrimaryKey | DefaultValue |
# +------------+----------+--------+------------+--------------+--------------+
# | Id         | INTEGER  | 0      | False      | True         |              |
# | Name       | TEXT     | 0      | False      | False        |              |
# | Email      | TEXT     | 0      | True       | False        |              |
# | CreatedAt  | TIMESTAMP| 0      | True       | False        | CURRENT_TIME |
# +------------+----------+--------+------------+--------------+--------------+

JSON Output

bash
# Get column info as JSON for programmatic use
export DBCLI_CONNECTION="Data Source=app.db"
dbcli columns Users

# Output: [
#   {"ColumnName":"Id","DataType":"INTEGER","Length":0,"IsNullable":false,"IsPrimaryKey":true,"DefaultValue":""},
#   {"ColumnName":"Name","DataType":"TEXT","Length":0,"IsNullable":false,"IsPrimaryKey":false,"DefaultValue":""},
#   ...
# ]

Multiple Tables

bash
# Check structure of multiple tables
export DBCLI_CONNECTION="Data Source=app.db"
for table in Users Orders Products; do
    echo "=== $table ==="
    dbcli -f table columns $table
    echo
done

Use Cases

1. Database Discovery

bash
# First, see what tables exist
export DBCLI_CONNECTION="Data Source=unknown.db"
dbcli -f table tables

# Then examine interesting tables
dbcli -f table columns Users
dbcli -f table columns Orders

2. Schema Documentation

bash
#!/bin/bash
# Generate schema documentation

export DBCLI_CONNECTION="Data Source=app.db"
OUTPUT="schema_doc.txt"

echo "Database Schema Documentation" > $OUTPUT
echo "Generated: $(date)" >> $OUTPUT
echo >> $OUTPUT

# List all tables
echo "=== Tables ===" >> $OUTPUT
dbcli -f table tables >> $OUTPUT
echo >> $OUTPUT

# Get structure for each table
dbcli tables | jq -r '.[].TableName' | while read table; do
    echo "=== Table: $table ===" >> $OUTPUT
    dbcli -f table columns $table >> $OUTPUT
    echo >> $OUTPUT
done

echo "Documentation saved to $OUTPUT"

3. Verify Table Exists

bash
# Check if specific table exists
export DBCLI_CONNECTION="Data Source=app.db"
if dbcli tables | jq -r '.[].TableName' | grep -q "^Users$"; then
    echo "Users table exists"
else
    echo "Users table not found"
fi

4. Find Tables by Pattern

bash
# Find all tables starting with "temp_"
export DBCLI_CONNECTION="Data Source=app.db"
dbcli tables | jq -r '.[].TableName' | grep "^temp_"

5. Column Validation

bash
# Check if Email column exists in Users table
export DBCLI_CONNECTION="Data Source=app.db"
if dbcli columns Users | jq -r '.[].ColumnName' | grep -q "^Email$"; then
    echo "Email column exists"
else
    echo "Email column missing - need to add it"
fi

6. Primary Key Detection

bash
# Find primary key column(s)
export DBCLI_CONNECTION="Data Source=app.db"
dbcli columns Users | jq -r '.[] | select(.IsPrimaryKey == true) | .ColumnName'

# Output: Id

7. Nullable Column Check

bash
# List all nullable columns
export DBCLI_CONNECTION="Data Source=app.db"
dbcli columns Users | jq -r '.[] | select(.IsNullable == true) | .ColumnName'

Programmatic Usage

Python - List All Tables

python
import subprocess
import json

# Assumes appsettings.json is in the current working directory (DbCli auto-loads it)
result = subprocess.run(['dbcli', 'tables'], capture_output=True, text=True, check=True)

tables = json.loads(result.stdout)
for table in tables:
    print(f"Table: {table['TableName']}")

Python - Inspect Schema

python
import subprocess
import json

def get_table_info(table_name):
    """Get detailed table information"""
    result = subprocess.run(
        ['dbcli', 'columns', table_name],
        capture_output=True,
        text=True,
        check=True,
    )

    columns = json.loads(result.stdout)

    print(f"\nTable: {table_name}")
    print(f"Total columns: {len(columns)}")

    print("\nPrimary Keys:")
    for col in columns:
        if col['IsPrimaryKey']:
            print(f"  - {col['ColumnName']} ({col['DataType']})")

    print("\nNullable Columns:")
    for col in columns:
        if col['IsNullable']:
            print(f"  - {col['ColumnName']}")

# Usage
get_table_info('Users')

Node.js - Schema Exploration

javascript
const { execSync } = require('child_process');

function exploreDatabaseSchema(connection) {
    // Set connection
    process.env.DBCLI_CONNECTION = connection;
    
    // Get all tables
    const tablesJson = execSync(`dbcli tables`).toString();
    const tables = JSON.parse(tablesJson);

    console.log(`Found ${tables.length} tables:\n`);

    tables.forEach(table => {
        console.log(`Table: ${table.TableName}`);

        // Get columns for each table
        const columnsJson = execSync(
            `dbcli columns ${table.TableName}`
        ).toString();
        const columns = JSON.parse(columnsJson);

        columns.forEach(col => {
            const pk = col.IsPrimaryKey ? ' [PK]' : '';
            const nullable = col.IsNullable ? ' [NULL]' : ' [NOT NULL]';
            console.log(`  - ${col.ColumnName}: ${col.DataType}${pk}${nullable}`);
        });

        console.log();
    });
}

// Usage
exploreDatabaseSchema('Data Source=app.db');

PowerShell - Schema Comparison

powershell
function Compare-DatabaseSchemas {
    param(
        [string]$Connection1,
        [string]$Connection2
    )

    $env:DBCLI_CONNECTION = $Connection1
    $tables1 = dbcli tables | ConvertFrom-Json | Select-Object -ExpandProperty TableName
    
    $env:DBCLI_CONNECTION = $Connection2
    $tables2 = dbcli tables | ConvertFrom-Json | Select-Object -ExpandProperty TableName

    Write-Host "Tables only in Database 1:"
    $tables1 | Where-Object { $_ -notin $tables2 }

    Write-Host "`nTables only in Database 2:"
    $tables2 | Where-Object { $_ -notin $tables1 }

    Write-Host "`nCommon tables:"
    $common = $tables1 | Where-Object { $_ -in $tables2 }
    $common

    # Compare column structure for common tables
    foreach ($table in $common) {
        $env:DBCLI_CONNECTION = $Connection1
        $cols1 = dbcli columns $table | ConvertFrom-Json
        
        $env:DBCLI_CONNECTION = $Connection2
        $cols2 = dbcli columns $table | ConvertFrom-Json

        if (Compare-Object $cols1 $cols2 -Property ColumnName, DataType) {
            Write-Host "`nDifference in table: $table"
        }
    }
}

# Usage
Compare-DatabaseSchemas -Connection1 "Data Source=db1.db" -Connection2 "Data Source=db2.db"

Output Formats

JSON Format (Default)

bash
export DBCLI_CONNECTION="Data Source=app.db"
dbcli tables
# [{"TableName":"Users"},{"TableName":"Orders"}]

dbcli columns Users
# [{"ColumnName":"Id","DataType":"INTEGER","Length":0,...},...]

Table Format (Human-Readable)

bash
export DBCLI_CONNECTION="Data Source=app.db"
dbcli -f table tables
# +-----------+
# | TableName |
# +-----------+
# | Users     |
# +-----------+

dbcli -f table columns Users
# +------------+----------+--------+------------+--------------+
# | ColumnName | DataType | Length | IsNullable | IsPrimaryKey |
# +------------+----------+--------+------------+--------------+

CSV Format

bash
export DBCLI_CONNECTION="Data Source=app.db"
dbcli -f csv tables > tables.csv
# TableName
# Users
# Orders

dbcli -f csv columns Users > users_schema.csv
# ColumnName,DataType,Length,IsNullable,IsPrimaryKey,DefaultValue
# Id,INTEGER,0,False,True,
# Name,TEXT,0,False,False,

Common Patterns

Quick Table Count

bash
export DBCLI_CONNECTION="Data Source=app.db"
dbcli tables | jq '. | length'

Find Large Tables

bash
# List tables with row counts
export DBCLI_CONNECTION="Data Source=app.db"
dbcli tables | jq -r '.[].TableName' | while read table; do
    count=$(dbcli query "SELECT COUNT(*) as cnt FROM $table" | jq -r '.[0].cnt')
    echo "$table: $count rows"
done

Generate CREATE TABLE from Existing

bash
# SQLite - Get original CREATE statement
export DBCLI_CONNECTION="Data Source=app.db"
dbcli query "SELECT sql FROM sqlite_master WHERE type='table' AND name='Users'"

Schema Diff Tool

bash
#!/bin/bash
# schema_diff.sh - Compare two database schemas

DB1="$1"
DB2="$2"

echo "Comparing schemas: $DB1 vs $DB2"

# Compare table lists
export DBCLI_CONNECTION="Data Source=$DB1"
tables1=$(dbcli tables | jq -r '.[].TableName' | sort)

export DBCLI_CONNECTION="Data Source=$DB2"
tables2=$(dbcli tables | jq -r '.[].TableName' | sort)

diff <(echo "$tables1") <(echo "$tables2")

Integration with Other Skills

Use with Query Skill

bash
# First, find all tables
export DBCLI_CONNECTION="Data Source=app.db"
tables=$(dbcli tables | jq -r '.[].TableName')

# Then query each table
for table in $tables; do
    echo "=== Sample from $table ==="
    dbcli -f table query "SELECT * FROM $table LIMIT 3"
done

Use with Export Skill

bash
# Export all tables found in database
export DBCLI_CONNECTION="Data Source=app.db"
dbcli tables | jq -r '.[].TableName' | while read table; do
    echo "Exporting $table..."
    dbcli export $table > "${table}_backup.sql"
done

Didn't find tool you were looking for?

Be as detailed as possible for better results