---

name: dbcli-export-schema description: Export database schema objects (procedures, functions, triggers, views, indexes) as SQL scripts for backup 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 supported-databases: "30+" operation-type: backup-export-schema safety-level: safe requires-backup: no tags: [dbcli, sql, backup, schema, ddl, export, procedures, functions, triggers, views, indexes] repository: https://github.com/your-repo/dbcli allowed-tools: dbcli

DbCli Schema Export Skill

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.

Overview

Export database schema objects (stored procedures, functions, triggers, views, indexes) as SQL DDL scripts for backup and recovery. This skill is essential for protecting database objects before DROP or ALTER operations.

Safety Level: Safe (read-only operation)
Requires Backup: No (this tool creates backups)

Quick Start

Copy# Export all schema objects
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli export-schema all -o backup_schema.sql

# Export all schema objects as separate files (per object)
dbcli export-schema all --output-dir ./schema_export

# Export only stored procedures
dbcli export-schema procedure -o backup_procedures.sql

# Export only indexes
export DBCLI_CONNECTION="Data Source=app.db"
dbcli export-schema index -o backup_indexes.sql

# Export only triggers
dbcli export-schema trigger -o backup_triggers.sql

# Filter by name pattern
export DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"
dbcli export-schema procedure --name "sp_User*"

---

Command Syntax

Basic Usage

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

dbcli export-schema <type> [options]

Arguments

• <type> - Object type to export:
  • all - All schema objects (default)
  • procedure - Stored procedures
  • function - User-defined functions
  • trigger - Triggers
  • view - Views
  • index - Indexes

Options

• -n, --name <pattern> - Filter objects by name (supports wildcards)
• -o, --output <file> - Save to file (default: console output)
• --output-dir <dir> - Save as separate files under a directory (one file per object)

Connection Options

• Environment variables:
  • DBCLI_CONNECTION: Database connection string
  • DBCLI_DBTYPE: Database type
• -t, --db-type <type> - Database type (sqlite, sqlserver, mysql, postgresql, etc.)
• --config <path> - Configuration file path

---

Examples

SQLite

Copyexport DBCLI_CONNECTION="Data Source=app.db"

# Export all triggers
dbcli export-schema trigger

# Export all indexes to file
dbcli export-schema index -o backup_indexes.sql

# Export all schema objects
dbcli export-schema all -o backup_all.sql

SQL Server

Copyexport DBCLI_CONNECTION="Server=.;Database=mydb;Trusted_Connection=True"
export DBCLI_DBTYPE="sqlserver"

# Export all stored procedures
dbcli export-schema procedure -o procedures_backup.sql

# Export specific procedures
dbcli export-schema procedure --name "sp_User*" -o user_procedures.sql

# Export all functions
dbcli export-schema function -o functions_backup.sql

# Export all triggers
dbcli export-schema trigger -o triggers_backup.sql

MySQL

Copyexport DBCLI_CONNECTION="Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx"
export DBCLI_DBTYPE="mysql"

# Export all procedures
dbcli export-schema procedure -o procedures.sql

# Export all triggers
dbcli export-schema trigger -o triggers.sql

# Export all indexes
dbcli export-schema index -o indexes.sql

PostgreSQL

Copyexport DBCLI_CONNECTION="Host=localhost;Database=mydb;Username=postgres;Password=xxxxxxxxxx"
export DBCLI_DBTYPE="postgresql"

# Export all functions
dbcli export-schema function -o functions.sql

# Export all triggers
dbcli export-schema trigger -o triggers.sql

# Export all views
dbcli export-schema view -o views.sql

---

Use Cases

1. Pre-deployment Backup

Copy# Backup all schema objects before deployment
# If appsettings.json is in the current working directory, DbCli auto-loads it
dbcli export-schema all -o "backup_$(date +%Y%m%d_%H%M%S).sql"

2. Procedure Refactoring

Copyexport DBCLI_CONNECTION="Server=.;Database=mydb"
export DBCLI_DBTYPE="sqlserver"

# 1. Backup existing procedures
dbcli export-schema procedure -o old_procedures.sql

# 2. Drop and recreate procedures
dbcli ddl "DROP PROCEDURE sp_OldProc"
dbcli ddl "CREATE PROCEDURE sp_NewProc..."

# 3. Rollback if needed (execute old_procedures.sql)

3. Index Optimization

Copyexport DBCLI_CONNECTION="Data Source=app.db"

# 1. Backup current indexes
dbcli export-schema index -o indexes_before.sql

# 2. Drop inefficient indexes
dbcli ddl "DROP INDEX idx_slow"

# 3. Create optimized indexes
dbcli ddl "CREATE INDEX idx_fast ON Table(col1, col2)"

# 4. Export new indexes
dbcli export-schema index -o indexes_after.sql

4. Database Migration

Copy# Export all schema from source database
export DBCLI_CONNECTION="Server=source;Database=db"
export DBCLI_DBTYPE="sqlserver"
dbcli export-schema all -o schema_export.sql

# Review and edit schema_export.sql if needed

# Apply to target database
# Execute the SQL scripts manually or via dbcli ddl command

5. Continuous Backup Script

Copy#!/bin/bash
# backup_schema.sh

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="./schema_backups"

mkdir -p $BACKUP_DIR

# Backup all schema objects
dbcli --config production.json \
  export-schema all \
  -o "$BACKUP_DIR/schema_${TIMESTAMP}.sql"

echo "Schema backed up to: $BACKUP_DIR/schema_${TIMESTAMP}.sql"

# Keep only last 30 days of backups
find $BACKUP_DIR -name "schema_*.sql" -mtime +30 -delete

---

Complete Backup & Restore Workflow

Backup Before DROP

Copy# Step 1: Export schema object
export DBCLI_CONNECTION="Data Source=app.db"
dbcli export-schema trigger -o backup_triggers.sql

# Step 2: Review backup file
cat backup_triggers.sql

# Step 3: Perform dangerous operation
dbcli ddl "DROP TRIGGER update_timestamp"

# Step 4: Verify deletion
dbcli export-schema trigger
# (should show no triggers)

Restore from Backup

Copy# Method 1: Extract and execute CREATE statement
TRIGGER_SQL=$(grep -A 10 "CREATE TRIGGER" backup_triggers.sql | sed '/^GO$/q')
dbcli ddl "$TRIGGER_SQL"

# Method 2: Execute entire backup file (if single object)
dbcli ddl -F backup_triggers.sql

# Verify restoration
dbcli export-schema trigger

---

Output Format

The exported SQL script includes:

Copy-- Schema Export for <DatabaseType>
-- Generated: YYYY-MM-DD HH:MM:SS
-- Object Type: <type>

-- ========================================
-- Stored Procedures
-- ========================================

-- Procedure: ProcedureName
CREATE PROCEDURE ProcedureName...
GO

-- ========================================
-- User Functions
-- ========================================

-- Function: FunctionName
CREATE FUNCTION FunctionName...
GO

-- ========================================
-- Triggers
-- ========================================

-- Trigger: TriggerName
CREATE TRIGGER TriggerName...
GO

-- ========================================
-- Views
-- ========================================

-- View: ViewName
CREATE VIEW ViewName...
GO

-- ========================================
-- Indexes
-- ========================================

-- Indexes for table: TableName
CREATE INDEX IndexName ON TableName(Column);

---

Database Support

Legend:

• ✅ Fully supported
• ⚠️ Partial support (may vary)
• ❌ Not supported

---

Best Practices

1. Always Backup Before DROP

Copy# ❌ BAD: Drop without backup
dbcli ddl "DROP PROCEDURE sp_ImportantLogic"

# ✅ GOOD: Backup first
export DBCLI_CONNECTION="..."
dbcli export-schema procedure --name "sp_ImportantLogic" -o backup.sql
dbcli ddl "DROP PROCEDURE sp_ImportantLogic"

2. Use Timestamped Backups

Copy# Create timestamped backup files
export DBCLI_CONNECTION="..."
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
dbcli export-schema all -o "schema_backup_${TIMESTAMP}.sql"

3. Store Backups in Version Control

Copy# Export to version-controlled directory
export DBCLI_CONNECTION="..."
dbcli export-schema all -o "database/schema/procedures.sql"
git add database/schema/procedures.sql
git commit -m "Backup procedures before refactoring"

4. Automate Daily Backups

Copy# Add to crontab (Linux) or Task Scheduler (Windows)
# Daily backup at 2 AM
0 2 * * * /usr/local/bin/dbcli --config /etc/dbcli.json export-schema all -o /backups/schema_$(date +\%Y\%m\%d).sql

5. Test Restore Procedures

Copy# Periodically test that backups can be restored
export DBCLI_CONNECTION="..."
dbcli export-schema trigger -o test_backup.sql
dbcli ddl "DROP TRIGGER test_trigger"
# Restore from backup...
# Verify restoration works

---

Error Handling

Common Errors

1. Connection Failed

   CopyError: Unable to connect to database
   

   • Check connection string
   • Verify database type (-t option)
   • Ensure database server is running

2. No Objects Found

   Copy-- Stored Procedures
   -- (empty)
   

   • Normal if no objects exist
   • Check database type compatibility
   • Verify name filter pattern

3. Permission Denied

   CopyError: Permission denied to read schema
   

   • Ensure database user has SELECT permissions on system tables
   • SQL Server: SELECT on sys.procedures, sys.sql_modules
   • MySQL: SELECT on INFORMATION_SCHEMA.ROUTINES
   • PostgreSQL: SELECT on pg_proc, pg_trigger

---

Integration with Other Skills

With dbcli-procedure

Copy# Backup before procedure changes
export DBCLI_CONNECTION="..."
dbcli export-schema procedure -o backup.sql
# Use dbcli-procedure skill to modify
dbcli ddl "ALTER PROCEDURE..."

With dbcli-index

Copy# Backup before index changes
export DBCLI_CONNECTION="..."
dbcli export-schema index -o backup.sql
# Use dbcli-index skill to drop/create
dbcli ddl "DROP INDEX..."

With dbcli-exec

Copy# Backup triggers before DML that might affect them
export DBCLI_CONNECTION="..."
dbcli export-schema trigger -o backup.sql
# Use dbcli-exec for data operations
dbcli exec "DELETE FROM..."

---

Configuration

Using Config File

Copy{
  "ConnectionString": "Server=.;Database=mydb;Trusted_Connection=True",
  "DbType": "sqlserver"
}

Copy# Export using config
# If appsettings.json is in the current working directory, DbCli auto-loads it
dbcli export-schema all -o backup.sql

Environment Variables

Copyexport DBCLI_CONNECTION="Server=.;Database=mydb;Trusted_Connection=True"
export DBCLI_DBTYPE="sqlserver"

dbcli export-schema all

---

Database DDL Extraction Capabilities

Different databases have varying levels of built-in support for extracting DDL (Data Definition Language):

Note: DbCli abstracts these differences and provides unified export-schema command across all supported databases. The implementation internally uses the appropriate method for each database type.

---

Tips & Tricks

1. Compare Schema Changes

Copy# Before changes
export DBCLI_CONNECTION="..."
dbcli export-schema procedure -o procedures_before.sql

# Make changes...

# After changes
dbcli export-schema procedure -o procedures_after.sql

# Compare
diff procedures_before.sql procedures_after.sql

2. Export Multiple Types

Copy# Export each type to separate files
export DBCLI_CONNECTION="..."
for type in procedure function trigger view index; do
  dbcli export-schema $type -o "backup_${type}.sql"
done

3. Grep for Specific Objects

Copy# Find all procedures containing "User"
export DBCLI_CONNECTION="..."
dbcli export-schema procedure | grep -i "user"

4. Create Deployment Package

Copy#!/bin/bash
# package_schema.sh

export DBCLI_CONNECTION="..."

mkdir -p deployment/schema
dbcli export-schema procedure -o deployment/schema/procedures.sql
dbcli export-schema function -o deployment/schema/functions.sql
dbcli export-schema trigger -o deployment/schema/triggers.sql
dbcli export-schema view -o deployment/schema/views.sql
dbcli export-schema index -o deployment/schema/indexes.sql

tar -czf schema_deployment.tar.gz deployment/

---

License

MIT License - See LICENSE file for details