Dbcli Tables

---
name: dbcli-tables
description: 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.
license: MIT
compatibility: Requires DbCli CLI tool (based on .NET 10 and SqlSugar). Supports Windows, Linux, macOS.
metadata:
  tool: dbcli
  version: "1.0.0"
  category: database
  operation-type: read-only
  supported-databases: "30+"
allowed-tools: dbcli
---


## 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
dbcli -c "CONNECTION_STRING" [-t DATABASE_TYPE] [-f FORMAT] tables
```

### Show Table Structure

```bash
dbcli -c "CONNECTION_STRING" [-t DATABASE_TYPE] [-f FORMAT] columns TABLE_NAME
```

## Global Options

- `-c, --connection`: Database connection string (required)
- `-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)
dbcli -c "Data Source=app.db" tables

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

# Table format (human-readable)
dbcli -c "Data Source=app.db" -f table tables

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

### Different Databases

```bash
# SQL Server
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver -f table tables

# MySQL
dbcli -c "Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx" -t mysql -f table tables

# PostgreSQL
dbcli -c "Host=localhost;Database=mydb;Username=postgres;Password=xxxxxxxxxx" -t postgresql -f table tables

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

# MongoDB
dbcli -c "mongodb://localhost:27017/mydb" -t mongodb -f table tables
```

### Chinese Domestic Databases

```bash
# DaMeng (达梦)
dbcli -c "Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb" -t dm -f table tables

# KingbaseES (人大金仓)
dbcli -c "Server=localhost;Port=54321;UID=system;PWD=xxxxxxxxxx;database=mydb" -t kdbndp -f table tables

# GaussDB
dbcli -c "Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx" -t gaussdb -f table tables
```

## Show Table Structure

### Basic Column Information

```bash
# SQLite - Show Users table structure
dbcli -c "Data Source=app.db" -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
dbcli -c "Data Source=app.db" 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
for table in Users Orders Products; do
    echo "=== $table ==="
    dbcli -c "Data Source=app.db" -f table columns $table
    echo
done
```

## Use Cases

### 1. Database Discovery

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

# Then examine interesting tables
dbcli -c "Data Source=unknown.db" -f table columns Users
dbcli -c "Data Source=unknown.db" -f table columns Orders
```

### 2. Schema Documentation

```bash
#!/bin/bash
# Generate schema documentation

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 -c "$CONNECTION" -f table tables >> $OUTPUT
echo >> $OUTPUT

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

echo "Documentation saved to $OUTPUT"
```

### 3. Verify Table Exists

```bash
# Check if specific table exists
if dbcli -c "Data Source=app.db" 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_"
dbcli -c "Data Source=app.db" tables | jq -r '.[].TableName' | grep "^temp_"
```

### 5. Column Validation

```bash
# Check if Email column exists in Users table
if dbcli -c "Data Source=app.db" 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)
dbcli -c "Data Source=app.db" columns Users | jq -r '.[] | select(.IsPrimaryKey == true) | .ColumnName'

# Output: Id
```

### 7. Nullable Column Check

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

## Programmatic Usage

### Python - List All Tables

```python
import subprocess
import json

result = subprocess.run([
    'dbcli', '-c', 'Data Source=app.db',
    'tables'
], capture_output=True, text=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(connection, table_name):
    """Get detailed table information"""
    result = subprocess.run([
        'dbcli', '-c', connection,
        'columns', table_name
    ], capture_output=True, text=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('Data Source=app.db', 'Users')
```

### Node.js - Schema Exploration

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

function exploreDatabaseSchema(connection) {
    // Get all tables
    const tablesJson = execSync(`dbcli -c "${connection}" 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 -c "${connection}" 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
    )

    $tables1 = dbcli -c $Connection1 tables | ConvertFrom-Json | Select-Object -ExpandProperty TableName
    $tables2 = dbcli -c $Connection2 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) {
        $cols1 = dbcli -c $Connection1 columns $table | ConvertFrom-Json
        $cols2 = dbcli -c $Connection2 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
dbcli -c "Data Source=app.db" tables
# [{"TableName":"Users"},{"TableName":"Orders"}]

dbcli -c "Data Source=app.db" columns Users
# [{"ColumnName":"Id","DataType":"INTEGER","Length":0,...},...]
```

### Table Format (Human-Readable)

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

dbcli -c "Data Source=app.db" -f table columns Users
# +------------+----------+--------+------------+--------------+
# | ColumnName | DataType | Length | IsNullable | IsPrimaryKey |
# +------------+----------+--------+------------+--------------+
```

### CSV Format

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

dbcli -c "Data Source=app.db" -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
dbcli -c "Data Source=app.db" tables | jq '. | length'
```

### Find Large Tables

```bash
# List tables with row counts
dbcli -c "Data Source=app.db" tables | jq -r '.[].TableName' | while read table; do
    count=$(dbcli -c "Data Source=app.db" 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
dbcli -c "Data Source=app.db" 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
diff <(dbcli -c "Data Source=$DB1" tables | jq -r '.[].TableName' | sort) \
     <(dbcli -c "Data Source=$DB2" tables | jq -r '.[].TableName' | sort)
```

## Integration with Other Skills

### Use with Query Skill

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

# Then query each table
for table in $tables; do
    echo "=== Sample from $table ==="
    dbcli -c "Data Source=app.db" -f table query "SELECT * FROM $table LIMIT 3"
done
```

### Use with Export Skill

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