Schemas

Work with views, procedures, functions and scripts

Beyond tables, a schema holds SQL objects: views, stored procedures, functions and free-form scripts. Each kind has its own tab in the schema workspace and its own detail page with a SQL editor.

In the app: Data Model › Schemas › your schema › Views, Procedures, Functions or Scripts tab

[screenshot]
A view's SQL tab: dialect tabs across the top, the editor, and the analysis panel below.

Before you start

Creating and editing SQL objects needs Read/Write access to the schema.

When to use each kind

  • Views are reusable read-only queries: cleansed projections, lookups, reporting surfaces. A view can be marked as materialized.
  • Stored procedures do work: insert, update, merge. Pipeline load procedures appear here.
  • Functions return a single value (scalar) or a set of rows (table-valued).
  • Scripts hold anything else, such as migration SQL or dialect-specific code.

The object lists

Each list shows the object name, Display Name, Description, DB Schema, who last modified it, and when it was created and modified. The Views and Functions lists also show Type.

  • Pipeline-generated objects are hidden by default on the Views, Procedures and Scripts tabs. Tick Show Generated to include them.
  • A red icon next to a name means the object has broken dependencies, for example a table it reads was deleted. Hover the icon for details. See AI in SQL object analysis.
  • Generate Descriptions and Delete … work on the selected rows. Deleted objects can be restored within 30 days, according to the confirmation.

Create a SQL object

  1. Open the tab and click Create View, Create Procedure, Create Function or Create Script.
  2. Fill in the dialog:
    • <Type> Name: required, up to 256 characters, unique in the schema. Views, procedures and functions need a valid SQL identifier. Script names can also contain spaces and hyphens.
    • Description (Optional).
    • Database Schema (Optional): overrides the schema’s default database schema, for example analytics. Not shown for scripts.
  3. Click Create …. The object’s page opens.

Note: Create Function starts the function as scalar. When the SQL is analyzed, Kenseme detects whether it’s actually table-valued and updates its type.

The object page

Each SQL object has these tabs:

Tab Views Procedures Functions Scripts
Info Yes Yes Yes Yes
SQL Yes Yes Yes Yes
Output Yes Yes Yes
Parameters Yes Yes
Dependencies Yes Yes Yes Yes
Attributes Yes Yes Yes Yes
Versions Yes Yes Yes Yes
  • Info has the name (Rename …), description with auto-generate, database schema, display name, the dependency summary and the Delete … button. Views also have VIEW TYPE, where you can tick Materialized View.
  • Output lists the columns the object returns, with type and nullability.
  • Parameters lists inputs with their direction.
  • Dependencies lists the tables, columns and objects the SQL reads or writes, with links.

Output, Parameters and Dependencies are filled in by analysis. See AI in SQL object analysis.

Write the SQL

The SQL tab holds the object’s code. A new object has no SQL yet: click Add Dialect, pick a Database Dialect, and start writing.

  • Type or paste SQL into the editor. It offers completion for the schema’s tables and columns.
  • Open the Schema browser on the side to find tables and columns and insert their names at the cursor.
  • Unsaved changes appears once you edit. Click Save.
  • After you save the primary dialect, Kenseme asks whether to analyze the script now. Click Analyze Now or Skip.
  • Copy copies the SQL.

Keep SQL in more than one dialect

An object can hold SQL for several dialects. Each dialect appears as a tab above the editor.

  • One dialect is primary, marked with a star. It is the one you edit. Use the star on another tab to Set as primary.
  • Adding a dialect converts the primary SQL with AI. Click Add Dialect and pick the target. Kenseme shows “Converting SQL to …” and fills the new tab. If the conversion fails, you get a template instead, with the message “AI conversion was not successful. A template has been created instead.”
  • Other dialects are read-only. They show “Read-only — generated from primary dialect”. Conversion warnings appear above the editor when there are any.
  • When the primary changes, other dialects show “Out of sync with the primary script”. Click Re-sync to convert again.
  • Remove a dialect with the remove icon on its tab.

Warning: AI conversion is a starting point. Read converted SQL before you rely on it, especially for vendor-specific syntax.

See Dialect-specific output notes for how the eight dialects differ.