Schemas

AI in SQL object analysis

Kenseme reads the SQL of each view, stored procedure, function and script with AI to understand what it does. The results fill in the object’s Output, Parameters and Dependencies tabs, draw the dependency lines on the schema diagram, and point out problems in the SQL.

In the app: Data Model › Schemas › your schema › a view, procedure, function or script › SQL tab

[screenshot]
The analysis panel under a view's SQL, showing Issues, Dependencies and Output Columns.

What analysis finds

The panel below the SQL editor shows up to five sections:

  • Formatting updates: changes analysis made to the script, such as expanding SELECT * into an explicit column list or aliasing duplicate column names.
  • Issues: problems in the SQL, ordered errors first, then warnings.
  • Dependencies: the tables, columns and other objects the SQL reads or writes.
  • Output columns: for views and functions that return rows, the columns produced and their types.
  • Column sources: where each output column comes from.

Parameters for procedures and functions appear on the object’s Parameters tab. For functions, analysis also works out whether the function is scalar or table-valued and updates its type.

Note: Analysis saves a formatted version of your script. Check the Formatting updates list after it runs so no change surprises you.

When analysis runs

  • When you save. After you save the primary dialect’s SQL, Kenseme asks Would you like to analyze this script now? Click Analyze Now, or Skip to leave it for later.
  • When objects are created for you. Objects created by a script import or accepted from the Schema Agent are queued for analysis automatically.
  • When you ask. If a script has never been analyzed, a banner says Script has not been analyzed yet with Run analysis. If it changed since the last analysis, the banner says so and offers Re-analyze.

While analysis runs, the tab shows Analyzing script... with a Cancel button. Analysis also appears on the Background Jobs tab in Organization Settings.

Reading the result

  • Analysis complete with a time means the results match the current SQL.
  • Analysis out of sync with current script means you’ve edited since. Click Analyze now to refresh.
  • Analysis failed means the AI couldn’t make sense of the SQL. The object is still saved; only the analyzed details are missing. Simplify or correct the SQL and click Re-analyze.

Tip: An object that has never been analyzed has empty Output and Dependencies tabs and draws no dependency lines on the diagram. If the diagram says “No dependency data yet”, analyze a few objects.

Broken dependencies

Kenseme watches for SQL objects whose dependencies no longer exist, for example a view reading a table you deleted. Such objects show a red icon next to their name on the Views, Procedures, Functions and Scripts lists. Hover the icon to see what’s wrong (“This object has broken dependencies” or a more specific message).

Kenseme checks this when you delete tables, columns or SQL objects, and can also re-check every schema on a schedule when your installation has that turned on. The scheduled check skips organizations in read-only mode.

To clear the flag, fix the SQL or restore what it depends on, then re-analyze.

What you control

  • Whether to analyze after each save.
  • Whether to re-analyze after edits.
  • The SQL itself. Analysis can reformat the saved script (see Formatting updates) but lists every change it makes, so you can review it.