Last updated: 2026-07-11

Scripting Objects

Object scripting in Jam SQL Studio generates DDL from a live database object — a table, view, stored procedure, or function — as a CREATE, ALTER, or DROP statement. Right-click any object in the Object Explorer, pick a script action, and the generated SQL opens in a new query tab ready to review, edit, run, or save. Scripting is engine-aware across SQL Server, PostgreSQL, MySQL/MariaDB, Oracle, and SQLite.

What is object scripting?

Scripting reverse-engineers an object's current definition back into SQL. It's how you capture a table's exact shape for source control, hand a colleague a repeatable CREATE, stage an ALTER to tweak a procedure, or generate a safe DROP before a rebuild. Every script action in Jam SQL Studio ends the same way: the SQL is placed in a new Query Editor tab — never executed silently — so you always see and control what runs. From that tab you can run it, edit it, or save it to a .sql file.

Where scripting lives

Scripting is driven from the Object Explorer context menus. The exact menu depends on the object type:

Tables don't offer a direct ALTER because a whole-table alter isn't a single statement — use the Table Designer for in-place column changes, or script DROP + CREATE for a full rebuild.

Script as CREATE

Generate a complete object definition as a CREATE statement — useful for version control, documentation, or recreating an object in another database.

Scripting a Table

1. Expand the database in Object Explorer and open Tables
2. Right-click your table and open Script table as
3. Choose CREATE
4. A new query tab opens with the generated CREATE TABLE script

Jam SQL Studio fetches the table's metadata and builds a CREATE TABLE that includes:

• Column definitions - name, data type, nullability, defaults
• Primary key - including clustered/nonclustered where the engine has the concept
• Foreign key constraints - with referenced table and columns
• Unique and check constraints
• Indexes - type and columns
• Identity / auto-increment - seed and increment where applicable

Scripting Views, Procedures, and Functions

For programmable objects, Jam SQL Studio reads the stored module definition (for example from sys.sql_modules on SQL Server or pg_get_functiondef on PostgreSQL) and returns the real source:

• Views — the full CREATE VIEW with its SELECT body and options such as SCHEMABINDING. Open definition does the same and is handy for a quick read-only look.
• Stored procedures — the complete definition with parameters, defaults, and any WITH options.
• Functions — scalar, table-valued, and inline table-valued functions, including the RETURNS clause and body.

Script as INSERT

Generate an INSERT template for a table — a starting point for writing new rows, not a data-migration script. Right-click the table, open Script table as, and choose INSERT; a new query tab opens with an INSERT INTO statement listing every column, ready for you to fill in values.

The template always leaves identity / auto-increment columns out, since the engine assigns those values on insert — there's no option to include them here. If you need scripts that carry actual row data with real key values (including identity values), use Copy INSERT or Export SQL (INSERT) from the Query Editor results grid or the Table Explorer toolbar instead — those are identity-aware and offer a Skip identity columns option.

Script as ALTER

Generate an ALTER statement to modify an existing programmable object without dropping and recreating it. ALTER preserves the object's permissions, whereas DROP+CREATE removes them — so ALTER is the safer edit for anything with grants attached.

ALTER scripting is available for views, stored procedures, and functions. To use it, right-click the object, open Script … as, and choose ALTER. On Oracle, packages, procedures, functions, and views are re-emitted with CREATE OR REPLACE (see below), which serves the same "edit without dropping" purpose.

Script as DROP

Generate a DROP statement to remove an object. Jam SQL Studio emits engine-appropriate, safety-first DROP syntax:

• SQL Server, PostgreSQL, MySQL, SQLite — DROP <type> IF EXISTS <qualified name>, so re-running a script on a database where the object is already gone doesn't error.
• PostgreSQL routines — the DROP includes the argument signature (e.g. DROP FUNCTION IF EXISTS public.calc(integer, integer)) so overloaded functions are dropped unambiguously.
• Oracle — a plain DROP (Oracle has no IF EXISTS clause). Individual package members can't be dropped on their own, so Jam SQL Studio scripts a DROP PACKAGE with an explanatory comment instead.

-- SQL Server / PostgreSQL / MySQL / SQLite
DROP TABLE IF EXISTS [dbo].[Customers];

-- Oracle (no IF EXISTS)
DROP TABLE HR.CUSTOMERS;

Execute Templates for Stored Procedures

Stored procedures have a dedicated Execute… action (separate from the Script as menu). It reads the procedure's parameters and opens a parameterized call template in a new query tab — it does not auto-run, so you fill in values and execute when ready.

The template is engine-specific. On SQL Server you get an EXEC with each parameter and OUTPUT markers where relevant:

-- Execute stored procedure [dbo].[GetCustomerOrders]
-- Parameters:
--   @CustomerID int
--   @StartDate datetime
--   @EndDate datetime
--   @OrderStatus varchar (OUT)

EXEC [dbo].[GetCustomerOrders]
  @CustomerID = <value>,
  @StartDate = <value>,
  @EndDate = <value>,
  @OrderStatus = <value> OUTPUT

On PostgreSQL the template uses CALL with named arguments; on Oracle it wraps the call in a BEGIN … END; block (and qualifies package members with the package name). Each variant lists the parameters as comments so you can see types and directions at a glance.

Cross-Engine Scripting: SQL Server, PostgreSQL, MySQL, Oracle, SQLite

Scripting isn't a SQL-Server-only feature. Each engine has its own script provider, so the generated SQL always matches the dialect of the connection you're on.

Database- and Schema-Level Scripting

Script Database As

Right-click a database in Object Explorer and choose Script Database As, then CREATE, DROP, or USE.

Script Schema As

When the Object Explorer groups objects by schema, each schema node has its own scripting actions:

• Script New — opens a new query tab with a CREATE template (Table, View, Stored Procedure, or Function) pre-scoped to that schema, so the template already targets schema.name.
• Script Schema As → CREATE / DROP — generates a CREATE SCHEMA or DROP SCHEMA statement. Available on SQL Server and PostgreSQL, where schemas are first-class objects.
• Script Schema As → Script all objects — generates CREATE statements for every table, view, procedure, and function in the schema in a single query tab (tables first, then views, then procedures and functions).

Oracle-Specific Scripting

Oracle exposes object types you won't find on SQL Server or PostgreSQL, and Jam SQL Studio scripts each in the right form.

On Oracle 23ai+ and PostgreSQL 19+, SQL property graphs appear under the Property Graphs node in the Object Explorer. Right-click a graph to Script as CREATE (full CREATE PROPERTY GRAPH DDL via DBMS_METADATA on Oracle, pg_get_propgraphdef on PostgreSQL), Script as DROP, or Query with GRAPH_TABLE — which opens a SELECT … FROM GRAPH_TABLE(…) starter query pre-filled with labels and sample properties read from the graph's definition. For the SQL/PGQ syntax itself — and what each engine's dialect accepts — see SQL/PGQ in Practice.

PostgreSQL-Specific Scripting (PostgreSQL 19, beta)

PostgreSQL tables get two additional script menus, both PostgreSQL 19-only in what they generate — but the menu items themselves are always shown, so the script itself is what tells you a feature needs PostgreSQL 19+.

Maintenance menu

Right-click a PostgreSQL table and open Maintenance for four ready-to-run scripts, each opened in a new query tab (never executed automatically):

• Script VACUUM (ANALYZE) and Script ANALYZE — work on any PostgreSQL version.
• Script REPACK and Script REPACK CONCURRENTLY — PostgreSQL 19's REPACK replaces the old VACUUM FULL / CLUSTER pattern for reclaiming space without a full table rewrite lock. On a server older than PostgreSQL 19 (or when the version can't be detected), the generated script includes a -- NOTE: REPACK requires PostgreSQL 19+ (this server reports …) comment instead of hiding the menu item.

Partitions: Select Top and MERGE/SPLIT script templates

Declarative-partitioned tables (PARTITION BY RANGE/LIST/HASH) show a Partitions folder in Object Explorer listing each child partition with its FOR VALUES … bound inline in the tree. Right-click a partition for:

• Select Top 1000 — a plain SELECT against that partition, works on any PostgreSQL version.
• Script SPLIT PARTITION (PostgreSQL 19+) — opens a query tab prefilled with the partition's current bound as a comment and TODO-marked placeholder bounds for the two new halves; you fill in the split point before running it.

Right-click the Partitions folder itself for Script MERGE PARTITIONS (PostgreSQL 19+) — generates an ALTER TABLE … MERGE PARTITIONS (…) INTO … statement that merges every current sibling partition into a new <parent>_merged partition, ready to run as-is.

Scripting in Jam SQL Studio vs SSMS's Generate Scripts wizard

SQL Server Management Studio's Generate Scripts… wizard is a multi-page, batch-oriented flow: pick objects, step through advanced options, then produce one big script file or window. Jam SQL Studio takes a lighter, per-object approach that also spans five engines:

• One right-click, one object. Script exactly the object you're looking at as CREATE, ALTER, or DROP — no wizard, no page-through.
• Always lands in an editable query tab. The script opens in the normal Query Editor with syntax highlighting, so you refine and run it in place rather than exporting a file first.
• Cross-engine by design. The same actions work on PostgreSQL, MySQL, Oracle, and SQLite, each with dialect-correct output — where the SSMS wizard is SQL Server only.
• Whole-schema when you need it. For a batch, Script Schema As → Script all objects emits every object in a schema in one pass.

For structural diffs and synchronization scripts across two databases, pair scripting with Schema Compare, which generates the ALTER/CREATE/DROP statements needed to make one schema match another.

Best Practices

Version Control

• Script objects and save the query tab to a .sql file committed to source control
• Use consistent naming for script files
• Keep schema prefixes in object names for clarity
• Add a comment header with change history

Deployment Scripts

• Rely on the built-in IF EXISTS guards on DROP scripts (and add IF NOT EXISTS where your engine supports it)
• Test scripts in development before production
• Wrap multi-statement deployments in a transaction
• Script in dependency order — check the Dependency Viewer first

Documentation and Backups

• Use scripted objects as living documentation of the database structure
• Generate a CREATE script before a major change as a quick rollback reference
• Combine with Schema Compare for change tracking between environments

Frequently asked questions