---
id: database--database-schema
title: "Schema reader"
summary: "Reads the schema of database objects - tables, views, procedures and functions - and emits it as descriptors: columns with types, keys and foreign keys for tables and views, parameters for routines. Its main use is the branch of an **AI tool**, so the model can look up tables before it writes SQL; it also serves to document a database or drive generic flows."
audience: user
keywords: "input, output, connectionstring, searchterm, includetables, includeviews, includeprocedures, includefunctions, outputcollection, maxitemstoreturn, cacheseconds"
---
# Schema reader

Reads the schema of database objects - tables, views, procedures and functions - and emits it as descriptors: columns with types, keys and foreign keys for tables and views, parameters for routines. Its main use is the branch of an **AI tool**, so the model can look up tables before it writes SQL; it also serves to document a database or drive generic flows.

## Pins

| Pin | Kind | Types | Description |
|---|---|---|---|
| Input | input | any | Triggers the read. |
| Output | output | `Viaducta.Plugins.DatabaseSchema.Types.DatabaseObjectDescriptor` or `Viaducta.Plugins.DatabaseSchema.Types.DatabaseSchemaContainer` | One descriptor per object, or one container with all of them when `OutputCollection` is `true`. |

## Configuration

| Field | Type | Default | Description |
|---|---|---|---|
| `ConnectionString` | database link name | | Required. Name of a repository database link (link selector). Looked up by exact name; a raw connection string or `#name` prefix is not resolved. |
| `SearchTerm` | string | | Object filter, written `object` or `schema.object`; `*` is a wildcard and the object part matches anywhere in the name (`sales.cust` finds `sales.customers` and `sales.old_customers`). Without a schema all schemas are searched. Empty = all objects. |
| `IncludeTables` | boolean | `true` | Include tables. |
| `IncludeViews` | boolean | `true` | Include views. |
| `IncludeProcedures` | boolean | `true` | Include stored procedures. |
| `IncludeFunctions` | boolean | `true` | Include functions. |
| `OutputCollection` | boolean | `false` | `true` emits one `DatabaseSchemaContainer`; `false` emits one `DatabaseObjectDescriptor` per object. Triggers reconfigure. |
| `MaxItemsToReturn` | integer 1-1000 | `20` | Maximum objects per category (4 categories = up to 4x this number). |
| `CacheSeconds` | integer | `300` | How long a read schema is reused for the same settings and search term. `0` reads the database on every input. |

## Behaviour

- Reconfigure (changing `OutputCollection`) sets the `Output` type to the container or the single descriptor.
- Per input the selected categories are listed (up to `MaxItemsToReturn` each) and each object is described. The database connection is released before anything is emitted.
- `DatabaseObjectDescriptor`: `Name` (`schema.object`, quoted as the database needs it, e.g. `sales."orders"` on PostgreSQL), `ShortName` and `NameSpace` (schema) unquoted, `Kind` (`Table`, `View`, `Procedure`, `Function`), and
  - tables and views: `Columns` with `Name`, `DbType`, `IsNullable`, `Ordinal`, `IsPrimaryKey`, `IsGeneratedValue` (identity or computed) and `ForeignKey` (`ReferencedSchema`, `ReferencedTable`, `ReferencedColumn`);
  - procedures and functions: `Inputs` and `Outputs` with `Name`, `DbType`, `IsNullable`, `Ordinal`.
- In JSON, fields that do not apply are left out: `Columns` for routines, `Inputs`/`Outputs` for tables, `IsPrimaryKey`/`IsGeneratedValue` when false, `ForeignKey` when there is none.
- `DatabaseSchemaContainer`: `DatabaseName`, the four `Include...` flags, `HasMore` and `DatabaseObjects`. `HasMore` is `true` when a category had more objects than `MaxItemsToReturn`; narrow `SearchTerm` to see the rest.
- An object that cannot be described (unsupported column type, dropped meanwhile, no permission) is skipped with a warning in the log; the others are still emitted. Overloaded routines appear once.
- With `CacheSeconds` above `0`, the result is kept per component and per combination of link, search term, categories and limit. Changes to the database show up after the time runs out.

## Notes

- There is no `Continue` pin; connect follow-up logic to `Output` (with `OutputCollection` = `true` it is called exactly once per input).
- In an AI tool branch use `OutputCollection` = `true`, so the tool returns one result, and bind `SearchTerm` to a tool argument so the model can search.
- Foreign keys are read for SQL Server and DB2 only; other databases return columns without `ForeignKey`. `Description` is not filled in yet.
- Overloaded routines: PostgreSQL returns one of the overloads; Db2 and Snowflake skip them with a warning. Oracle packaged routines appear as `schema.package.routine`.
- Which objects come back when the limit cuts a list is decided by the database, not by name. Describing an object takes several metadata queries; keep `MaxItemsToReturn` small on large databases and leave the cache on.

## Example

```json
{ "ConnectionString": "MainDb", "SearchTerm": "{input.TableName}", "IncludeTables": true, "IncludeViews": true, "IncludeProcedures": false, "IncludeFunctions": false, "OutputCollection": true, "MaxItemsToReturn": 20, "CacheSeconds": 300 }
```

AI tool (Output, type with a `TableName` string) → **Schema reader** → AI tool (Input, type `Viaducta.Plugins.DatabaseSchema.Types.DatabaseSchemaContainer`)