DDL source navigation

functional

Open an object's definition right from the query instead of going on a tree-hunting expedition.

Reading a query and need to see what an object really looks like? Cmd-click on macOS or Ctrl-click on Windows/Linux to open its definition directly. No tree hunting and no emergency sp_help required. Hold the modifier first and resolvable names underline, so you know what will open.

What you can click

Tables, views, stored procedures, functions, and user-defined types. You can also open the same view from the data-model rows in the open dialog.

SQLly
SQLly DDL source viewer opened from a Cmd-click in the editorSQLly DDL source viewer opened from a Cmd-click in the editor
Cmd/Ctrl-clicking a table name in the editor opens its read-only, syntax-highlighted definition.

What it shows

A read-only, syntax-highlighted definition, with a header naming the object, where the source came from, the database, and the connection:

  • Views, procedures, and functions - the server's own stored definition.
  • Tables - a CREATE TABLE with columns (type, nullability, identity, collation, defaults) and the primary key, then appended Indexes, Foreign Keys, and Triggers sections. It's labelled “Generated from catalog metadata” when SQLly builds it from the catalog rather than a stored definition.

Each section is best-effort: if one can't be read it appends a -- … unavailable comment instead of failing the whole view. A toolbar gives you Copy and Open as Query - the generated DDL is never dropped into an editable tab unless you ask.

PostgreSQL privileges

PostgreSQL gives some access to everyone by default, every new function or procedure can be executed by PUBLIC, for example, so a script that only re-creates an object would quietly hand back access you had taken away. On PostgreSQL, definitions for tables, views, materialized views, functions, and procedures end with a Privileges section: SQLly compares the object's privileges with PostgreSQL's defaults and writes a REVOKE for each default you removed and a GRANT (with WITH GRANT OPTION where it applies) for each privilege you added, the way pg_dump does. Run the script and the object comes back with the same privileges. An object that still has PostgreSQL's default privileges gets no section, whether it was never touched or its privileges were set back to the defaults - and a role that is really named "PUBLIC" is quoted so it is never mistaken for everyone. It makes no difference whether the object only has grants, only has a revoked default, or both.

-- Privileges
REVOKE EXECUTE ON FUNCTION billing.close_month(p_month date) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION billing.close_month(p_month date) TO finance;

Column privileges on a table or view are scripted after the table's own, one line per column and grantee, in the same form pg_dump uses: GRANT SELECT(salary), UPDATE(salary) ON TABLE hr.people TO payroll;. A privilege that was granted by a role other than the owner (a role holding WITH GRANT OPTION that passed access on) is replayed as that role, wrapped in SET SESSION AUTHORIZATION and RESET SESSION AUTHORIZATION, so the recreated privilege records the same grantor; running such a script needs a superuser or a member of that role, exactly as a pg_dump script does.

-- Privileges
GRANT SELECT ON TABLE hr.people TO hr_lead WITH GRANT OPTION;
GRANT SELECT(salary) ON TABLE hr.people TO payroll;
SET SESSION AUTHORIZATION hr_lead;
GRANT SELECT ON TABLE hr.people TO analysts;
RESET SESSION AUTHORIZATION;

The same section is added to Script as CREATE from the explorer, for one object or several; the roles it names are never mistaken for selected objects when SQLly orders the script. Dumps leave privileges out unless you ask, because the roles they name may not exist where the dump is restored: tick Include privileges (GRANT and REVOKE) in the Export Database as SQL dialog on a PostgreSQL connection, or pass --include-privileges to sqlly-cli dump-database. Native pg_dump dumps always carry their own privileges. Privileges on schemas, sequences, and types are not scripted, because SQLly does not generate definitions for those objects.

How to configure it

Under Settings › DDL navigation you can enable or disable it, choose the activation modifier, and pick where it opens - a new tab (the default), a side panel on the current tab, or a popup window. Include privileges in PostgreSQL definitions (on by default) controls the Privileges section above for the definition view and Script as CREATE.

Supported on SQL Server (the fullest - generated tables with index/FK/trigger sections, plus routines, views, and types), PostgreSQL, MySQL/MariaDB, and SQLite.