# ssot-schema-query

> Query table schema information from the SSOT database (ssot.db). Use this skill to get detailed column definitions, data types, primary keys, unique constraints, indexes, and foreign key relationships for specific tables. Replaces reading init/配下 directly.

- Author: Hiroshi Kataoka
- Repository: pri-Kataoka-Hiroshi/kfc_generator2
- Version: 20260126232234
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-07
- Source: https://github.com/pri-Kataoka-Hiroshi/kfc_generator2
- Web: https://mule.run/skillshub/@@pri-Kataoka-Hiroshi/kfc_generator2~ssot-schema-query:20260126232234

---

---
name: ssot-schema-query
description: Query table schema information from the SSOT database (ssot.db). Use this skill to get detailed column definitions, data types, primary keys, unique constraints, indexes, and foreign key relationships for specific tables. Replaces reading init/配下 directly.
---

# SSOT Schema Query

## Overview

Query detailed schema information directly from `ssot.db` database. This skill retrieves CREATE TABLE statements, column definitions, primary keys (PK), unique constraints (UK), indexes, and foreign key (FK) relationships without reading the large `init/`配下 file.

## When to Use This Skill

Use this skill when you need:
- Detailed column definitions (name, type, constraints) for a specific table
- Primary key (PK) information - single or composite keys
- Unique constraints (UK) for a table
- Index definitions for a table
- Foreign key (FK) relationships for a table
- CREATE TABLE statement for reference
- Understanding table structure before writing queries or INSERT statements

## Quick Start

```bash
# Query single table schema
python3 scripts/query_schema.py api_master

# Query multiple tables
python3 scripts/query_schema.py api_master api_fields table_master

# List all tables
python3 scripts/query_schema.py --list

# Query all tables matching a pattern
python3 scripts/query_schema.py --pattern "api_*"
```

## Usage Patterns

### Single Table Query

```bash
python3 scripts/query_schema.py api_master
```

Output:
```
================================================================================
TABLE: api_master
================================================================================

CREATE TABLE api_master (
  full_api_cd TEXT PRIMARY KEY,
  service_provider TEXT NOT NULL,
  api_cd TEXT NOT NULL,
  ...
  UNIQUE(service_provider, api_cd),
  CONSTRAINT fk_api_master_transaction_scope
    FOREIGN KEY (transaction_scope_cd)
    REFERENCES sys_mst_transaction_scope(transaction_scope_cd)
);

COLUMNS:
| # | Column Name   | Type    | Not Null | Default | PK |
|---|---------------|---------|----------|---------|-----|
| 1 | full_api_cd   | TEXT    |          |         | 1  |
| 2 | service_provider | TEXT | Yes      |         |    |
| 3 | api_cd        | TEXT    | Yes      |         |    |
...

PRIMARY KEY:
  (full_api_cd) [Single]

UNIQUE CONSTRAINTS:
| Name | Columns |
|------|---------|
| sqlite_autoindex_api_master_2 | (service_provider, api_cd) |

INDEXES:
| Name | Columns | Partial |
|------|---------|---------|
| idx_api_master_service_provider_api_cd | (service_provider, api_cd) |  |

FOREIGN KEYS:
| Column          | References           | On Delete |
|-----------------|----------------------|-----------|
| transaction_scope_cd | sys_mst_transaction_scope(transaction_scope_cd) | |
```

### Multiple Tables

```bash
python3 scripts/query_schema.py project_master stakeholder_master business_goal
```

### Pattern Matching

```bash
# All API-related tables
python3 scripts/query_schema.py --pattern "api_*"

# All mapping tables
python3 scripts/query_schema.py --pattern "mapping_*"

# All system master tables
python3 scripts/query_schema.py --pattern "sys_mst_*"
```

### List All Tables

```bash
python3 scripts/query_schema.py --list
```

### Output Formats

```bash
# Default: Human-readable format
python3 scripts/query_schema.py api_master

# Compact format (CREATE statement only)
python3 scripts/query_schema.py api_master --compact

# JSON format (for programmatic use)
python3 scripts/query_schema.py api_master --json
```

## Integration with CLAUDE.md

The CLAUDE.md file contains the complete list of 79 table names. When detailed schema information is needed:

1. Reference CLAUDE.md for table names in each category
2. Use this skill to query specific table details
3. Avoid reading the 33,000+ token init/配下 file

## Example Workflow

```bash
# 1. Check CLAUDE.md for table names in a category
# "API関連 (23): api_master, api_fields, ..."

# 2. Query the specific tables you need
python3 scripts/query_schema.py api_master api_fields

# 3. Use the schema information for your task
```

## Troubleshooting

### Database Not Found

```
Error: Database not found at SSOT_db/ssot.db
```

Solution: Run from project root or use `--db-path` option:
```bash
python3 scripts/query_schema.py api_master --db-path /path/to/ssot.db
```

### Table Not Found

```
Warning: Table 'unknown_table' not found in database
```

Solution: Check table name spelling or use `--list` to see available tables.

## Script Details

### Location
`scripts/query_schema.py`

### Features
- Query single or multiple tables
- Pattern matching with glob syntax
- Multiple output formats (human-readable, compact, JSON)
- Foreign key relationship display
- Column constraint information

### Dependencies
- Python 3.6+
- sqlite3 (standard library)
- fnmatch (standard library)

No external packages required.