Dbcli Tables

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

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

dbcli [-f FORMAT] tables

Show Table Structure

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

# 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

# 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

# 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

# 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

# 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

# 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

# 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

#!/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

# 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

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

5. Column Validation

# 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

# 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

# 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

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

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

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

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)

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)

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

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

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

Find Large Tables

# 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

# 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

#!/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

# 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

# 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