shanescott.sql-data-exporter 1.0.0

SQL Table Export CLI Tool

A .NET CLI tool that connects to SQL Server, inspects table structure, retrieves data, and exports everything to a structured JSON file with metadata and data rows.

Features

• 🔍 Complete Metadata Extraction: Captures table schema, column definitions, primary keys, foreign keys, and more
• 📊 Data Export: Exports table data as JSON with proper type conversion
• 🎯 Flexible Filtering: Support for row limits and WHERE clause filters
• ⚙️ Multiple Configuration Options: Connection string via CLI, environment variable, or config file
• 🚀 Easy to Use: Simple command-line interface with sensible defaults

Installation

Install as a Global Tool

# From the project directory
dotnet pack
dotnet tool install --global --add-source ./nupkg sql-table-export

# Or install from NuGet (when published)
dotnet tool install --global SqlTableExport

Build from Source

cd sql-table-export
dotnet build
dotnet run -- --help

Usage

Basic Usage

# Export a table (uses environment variable or config file for connection)
sql-table-export --table users

# Specify connection string
sql-table-export -c "Server=localhost;Database=MyDb;Integrated Security=true;" -t users

# With schema
sql-table-export -t employees -s hr

# With custom output path
sql-table-export -t customers -o ./exports/customers.json

Advanced Usage

# Limit number of rows
sql-table-export -t orders -l 100

# With WHERE filter
sql-table-export -t customers -w "country = 'USA' AND status = 'active'"

# Combined options
sql-table-export \
  -c "Server=prod-db;Database=Sales;User Id=sa;Password=***;" \
  -t transactions \
  -s sales \
  -l 1000 \
  -w "date >= '2025-01-01'" \
  -o ./reports/sales-transactions.json

Connection String Options

1. CLI Option (Highest Priority)

sql-table-export -c "Server=localhost;Database=MyDb;Integrated Security=true;" -t users

2. Environment Variable

# Windows PowerShell
$env:SQL_CONNECTION_STRING = "Server=localhost;Database=MyDb;Integrated Security=true;"

# Windows CMD
set SQL_CONNECTION_STRING=Server=localhost;Database=MyDb;Integrated Security=true;

# Linux/Mac
export SQL_CONNECTION_STRING="Server=localhost;Database=MyDb;User Id=sa;Password=***;"

# Then run
sql-table-export -t users

3. Configuration File (Lowest Priority)

Edit appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyDb;Integrated Security=true;"
  }
}

Command-Line Options

Output Format

The tool generates a JSON file with the following structure:

{
  "metadata": {
    "schema": "dbo",
    "tableName": "users",
    "exportedAt": "2025-11-25T21:04:00Z",
    "rowCount": 150,
    "totalRowsInTable": 1000,
    "primaryKeys": ["userId"],
    "foreignKeys": [
      {
        "columnName": "departmentId",
        "referencedSchema": "dbo",
        "referencedTable": "departments",
        "referencedColumn": "id"
      }
    ]
  },
  "columns": [
    {
      "name": "userId",
      "dataType": "int",
      "maxLength": null,
      "precision": 10,
      "scale": 0,
      "isNullable": false,
      "isPrimaryKey": true,
      "isForeignKey": false,
      "defaultValue": null,
      "isIdentity": true
    },
    {
      "name": "username",
      "dataType": "nvarchar",
      "maxLength": 50,
      "precision": null,
      "scale": null,
      "isNullable": false,
      "isPrimaryKey": false,
      "isForeignKey": false,
      "defaultValue": null,
      "isIdentity": false
    }
  ],
  "data": [
    {
      "userId": 1,
      "username": "john_doe",
      "email": "john@example.com"
    },
    {
      "userId": 2,
      "username": "jane_smith",
      "email": "jane@example.com"
    }
  ]
}

Data Type Conversion

SQL Server types are converted to JSON-compatible types:

Examples

Export User Table

sql-table-export -t users

Output: dbo.users.json

Export with Filter

sql-table-export -t orders -w "status = 'pending' AND created_date > '2025-01-01'"

Export Limited Rows

sql-table-export -t logs -l 500 -o ./recent-logs.json

Export from Different Schema

sql-table-export -t employees -s hr -o ./hr-data/employees.json

Troubleshooting

Connection Issues

Problem: "No connection string provided"

# Solution: Provide connection string via any method
sql-table-export -c "Server=localhost;..." -t users
# OR
$env:SQL_CONNECTION_STRING = "Server=localhost;..."

Problem: "Cannot open database"

# Solution: Check connection string and permissions
# Verify with sqlcmd:
sqlcmd -S localhost -d MyDb -E -Q "SELECT 1"

Table Not Found

Problem: "Table [dbo].[users] does not exist"

# Solution: Check table name and schema
sql-table-export -t users -s dbo  # Specify correct schema

Permission Denied

Problem: "SELECT permission denied"

# Solution: Ensure SQL user has SELECT permission
# Grant permission:
GRANT SELECT ON [schema].[table] TO [username]

Requirements

• .NET 8.0 or later
• SQL Server 2012 or later (or Azure SQL Database)
• Appropriate database permissions (SELECT on target tables)

Development

Build

dotnet build

Run Tests

dotnet test

Create Package

dotnet pack -c Release

Install Locally

dotnet tool install --global --add-source ./nupkg sql-table-export

Uninstall

dotnet tool uninstall --global sql-table-export

Project Structure

sql-table-export/
├── Commands/
│   └── ExportCommand.cs          # CLI command implementation
├── Configuration/
│   └── ConnectionStringProvider.cs  # Connection string resolution
├── Models/
│   ├── ColumnMetadata.cs         # Column metadata model
│   ├── ForeignKeyInfo.cs         # Foreign key model
│   ├── TableMetadata.cs          # Table metadata model
│   └── ExportResult.cs           # Export result model
├── Services/
│   ├── ISqlMetadataService.cs    # Metadata service interface
│   ├── SqlMetadataService.cs     # Metadata extraction
│   ├── IDataExportService.cs     # Export service interface
│   └── DataExportService.cs      # Data export logic
├── Program.cs                     # Entry point
├── appsettings.json              # Configuration file
└── sql-table-export.csproj       # Project file

License

MIT License

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Future Enhancements

• Support for multiple tables in one export
• Export to CSV, XML, or Parquet formats
• Support for PostgreSQL and MySQL
• Incremental exports
• Data masking/anonymization
• Progress bars for large exports
• Parallel processing