Skip to main content
VillageSQL stores extension metadata in system tables within the villagesql schema. Query this information via system tables or INFORMATION_SCHEMA views.
Never modify system tables directly. Use INSTALL EXTENSION, UNINSTALL EXTENSION, and other SQL commands to manage extensions.

​
System Tables

TablePurposeKey Columns
villagesql.extensionsInstalled extensions registryextension_name, extension_version, veb_sha256
villagesql.custom_columnsType usage trackingdb_name, table_name, column_name, type_name
villagesql.propertiesSystem propertiesproperty_name, property_value

​
villagesql.extensions

Tracks all installed extensions with version and integrity information. Schema: 
CREATE TABLE villagesql.extensions (
  extension_name VARCHAR(64) NOT NULL PRIMARY KEY,
  extension_version VARCHAR(64) NOT NULL,
  veb_sha256 VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query Example: 
SELECT extension_name, extension_version
FROM villagesql.extensions;
Sample Output: 
+------------------+-------------------+
| extension_name   | extension_version |
+------------------+-------------------+
| vsql_complex     | 1.0.0             |
| vsql_uuid        | 0.2.1             |
+------------------+-------------------+

​
villagesql.custom_columns

Tracks which table columns use custom types. Critical for preventing uninstall of extensions with active usage. Schema: 
CREATE TABLE villagesql.custom_columns (
  db_name VARCHAR(64) NOT NULL,
  table_name VARCHAR(64) NOT NULL,
  column_name VARCHAR(64) NOT NULL,
  type_name VARCHAR(64) NOT NULL,
  extension_name VARCHAR(64) NOT NULL,
  extension_version VARCHAR(64) NOT NULL,
  PRIMARY KEY (db_name, table_name, column_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query Example: 
-- Find all tables using custom types
SELECT
    db_name,
    table_name,
    column_name,
    type_name
FROM villagesql.custom_columns
ORDER BY db_name, table_name;

-- Check dependencies before uninstall
SELECT COUNT(*) as dependent_columns
FROM villagesql.custom_columns
WHERE extension_name = 'vsql_complex';
Sample Output: 
+---------+------------+-------------+-----------+
| db_name | table_name | column_name | type_name |
+---------+------------+-------------+-----------+
| mydb    | signals    | impedance   | COMPLEX   |
| mydb    | signals    | frequency   | COMPLEX   |
| testdb  | users      | user_id     | UUID      |
+---------+------------+-------------+-----------+

​
villagesql.properties

System properties and metadata, including schema version. Schema: 
CREATE TABLE villagesql.properties (
  property_name VARCHAR(64) NOT NULL PRIMARY KEY,
  property_value VARCHAR(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query Example: 
-- Check schema version
SELECT property_value
FROM villagesql.properties
WHERE property_name = 'schema_version';

​
System Views

​
INFORMATION_SCHEMA.EXTENSIONS

Public view for querying installed extensions. Recommended for applications and scripts. Schema: 
SELECT
    EXTENSION_NAME,
    EXTENSION_VERSION
FROM INFORMATION_SCHEMA.EXTENSIONS;
Source: Backed by villagesql.extensions table. Example: 
-- List all installed extensions
SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS;

-- Check specific extension version
SELECT EXTENSION_VERSION
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_complex';

​
Common Queries

​
Find Extension Dependencies

Check if an extension can be safely uninstalled: 
SELECT
    'Custom Types' as dependency_type,
    type_name as name
FROM villagesql.custom_columns
WHERE extension_name = 'vsql_complex'

UNION ALL

SELECT
    'Columns' as dependency_type,
    CONCAT(db_name, '.', table_name, '.', column_name) as name
FROM villagesql.custom_columns
WHERE extension_name = 'vsql_complex';

​
List All Custom Types with Usage

SELECT
    ct.type_name,
    ct.extension_name,
    e.extension_version,
    COUNT(cc.column_name) as usage_count
FROM villagesql.custom_columns ct
LEFT JOIN villagesql.custom_columns cc
    ON ct.type_name = cc.type_name
    AND ct.extension_name = cc.extension_name
JOIN villagesql.extensions e
    ON ct.extension_name = e.extension_name
GROUP BY ct.type_name, ct.extension_name, e.extension_version
ORDER BY usage_count DESC;

​
Find Tables Using Extension Types

SELECT DISTINCT
    cc.db_name,
    cc.table_name,
    GROUP_CONCAT(cc.column_name ORDER BY cc.column_name) as columns
FROM villagesql.custom_columns cc
WHERE cc.extension_name = 'vsql_complex'
GROUP BY cc.db_name, cc.table_name;

​
Next Steps

Managing Extensions

Monitor and troubleshoot extensions

Install Extensions

Add new extensions

Extension Architecture

Understand the internals

Available Extensions

Browse extension catalog