Skip to content

CLI Objects and Scripting

radix29 edited this page Oct 31, 2026 · 1 revision

← goSSMS Home · ← CLI

CLI — Objects and scripting

The object group browses the connection's database the way Object Explorer does — list, search, describe, read a module's source, walk dependencies, check indexes and statistics — and the script group answers exactly what Object Explorer's Script as ▸ would write. All nine commands are class read: they never need --allow, and nothing is ever run.

Which database, and which name

  • Where they look. An object or script command reads the connection's database — the profile's, or -d/--database — and the header's DATABASE names it. There is no per-command database argument.
  • Object names are split with the T-SQL lexer, so a [bracketed] or "quoted" part keeps its dots and doubled ]]: Sales.[Order.Detail]. Two parts are schema.name; three, database.schema.name, only when the database is the connection's (compared exactly), else a usage error naming -d. Four parts or an empty part (db..name) is a usage error.
  • A name alone is looked up in every schema: one match is that object; two or more is USAGE_ERROR listing them as schema.name; none is NOT_FOUND with up to five objects whose names contain it. It never assumes dbo. Names compare by the database's catalog collation — in a _CS_ one, sales.customer is not Sales.Customer (the hint says so).
  • Permissions. What the login cannot see is left out, not an error, as SQL Server's metadata visibility does. A read the server refuses outright is PERMISSION_DENIED (exit 9); a part of an answer that could not be read is NULL with a warning, exit 0.

object list — browse with a filter

gossms object list -p dev --type table --schema Sales
gossms object list -p dev --type procedure,function --name order -o json
gossms object list -p dev -d master --type view --name dm_exec --include-system

One row per object: schema, name, type, is_system, ordered by kind as --type lists them, then schema and name.

Option
--type table, view, procedure, function, trigger, synonym, sequence, type, schema — default all. A trigger is a table's or view's DML trigger; a type is an alias, table or CLR type
--schema <s> Equal, case-insensitive
--name <text> Contains, case-insensitive, no wildcards — Object Explorer's Filters Settings semantics
--include-system Also list what SQL Server ships: system tables, the sys schema's views/procedures/functions/CLR types, the system schemas

object search — find text anywhere

gossms object search -p dev "Customer"
gossms object search -p dev "CustomerID" --in column
gossms object search -p dev "GETDATE()" --in definition -o json

Case-insensitive and literal (%, _, [ are characters, not wildcards). Rows: schema, name, type, and with --in column the column and its data_type.

--in Looks in
name (default) Tables, views, procedures, functions and triggers whose name contains the text
definition The modules whose T-SQL contains it — an encrypted or CLR module has none to search
column The columns of tables and views whose name contains it

--in definition is the "who computes this column / who calls this procedure" answer without leaving the terminal; --in column finds a column across the whole database.

object describe — the property sheet

gossms object describe -p dev Sales.SalesOrderHeader
gossms object describe -p dev Sales.SalesOrderHeader -o json
gossms object describe -p dev dbo.uspGetBillOfMaterials

A record — schema, name, type, typeDesc, objectId, created, modified, parent (a trigger's or constraint's table) — plus what the kind adds (table rowCount from partition metadata; view isSchemaBound; function returns; trigger isEnabled/events; synonym baseObject; sequence's type, bounds, cycling, cache, currentValue), and the kind's named sets:

Kind Sets
table columns, keys, indexes, check_constraints, triggers
view columns, indexes, triggers
procedure / function parameters (+ returns in the record for a function)

columns carries column_id, name, data_type, nullable, identity (IDENTITY(seed,increment)), computed (the expression, PERSISTED marked), default, collation, primary_key. keys holds the primary key, unique constraints and foreign keys (references as [schema].[table] (cols), on_delete, on_update, disabled, not_trusted). A computed, default or check expression the login cannot see (no VIEW DEFINITION) is NULL, counted in a warning.

object definition — the module's source

gossms object definition -p dev Sales.vSalesPerson
gossms object definition -p dev dbo.uspGetBillOfMaterials -o json

The view/procedure/function/DML trigger's T-SQL as stored — CREATE OR ALTER, comments and all — not a generated script. In text it follows the record under === DEFINITION (n lines) ===, line breaks kept, never cut by --max-col-width; in JSON it is one string; markdown fences it as a code block. An encrypted module (or one lacking VIEW DEFINITION) and a CLR module answer definition: null with a warning, exit 0. Any other kind is exit 2.

object dependencies — View Dependencies

gossms object dependencies -p dev Reporting.usp_MonthlySales --direction uses
gossms object dependencies -p dev Sales.SalesOrderDetail --direction used-by --depth 3 -o json

One row per edge, breadth first, uses before used-by: direction, level, via (the object at the level before; the root at level 1), schema, name, type, schema_bound (a WITH SCHEMABINDING reference), caller_dependent (an unqualified name resolved as the owner would — another caller may get a different object).

  • uses: what this object's definition references; used-by: what references this object — --direction used-by --depth 3 is "what breaks if I change it".
  • --depth 1–5 (default 1). An object reached twice is followed once, so a cycle ends; a table's self-reference (its filtered index or statistic) is left out; other databases' and missing objects are not listed.
  • Needs SELECT on sys.sql_expression_dependencies (db_owner has it): without, exit 9.

object indexes — columns, usage, fragmentation

gossms object indexes -p dev Sales.SalesOrderDetail
gossms object indexes -p dev Sales.SalesOrderDetail --fragmentation limited -o json

One row per index, index_id order, the heap first (name NULL, type HEAP): definition columns (type, unique, primary_key, key_columns with DESC marked, included_columns, filter, disabled, hypothetical), then usage: user_seeks, user_scans, user_lookups, user_updates and the last time of each — counted since the server started, NULL with a warning without VIEW SERVER STATE.

--fragmentation limited|sampled|detailed adds avg_fragmentation_pct, page_count, fragment_count, avg_page_space_used_pct — off by default because it reads the index; limited never reads the leaf, so its page fullness is NULL. An unused index (user_seeks/scans/lookups all zero or NULL) with writes is the drop candidate; a fragmented, heavily scanned one is the rebuild candidate.

object stats — statistics health

gossms object stats -p dev Sales.SalesOrderDetail
gossms object stats -p dev Sales.SalesOrderDetail -o json

One row per statistics object: name, stats_id, columns, origin (index — an index keeps it; auto — the optimizer created it; user — CREATE STATISTICS), filter, last_updated, rows, rows_sampled, sample_pct, steps (histogram), modification_counter, no_recompute, incremental. A statistic never computed, or one the login may see but not read, has the properties NULL, counted in a warning. modification_counter against rows is the "auto-update should have fired" check.

script object / script db — Script As, to a file

gossms script object -p dev Sales.SalesOrderHeader
gossms script object -p dev dbo.uspGetBillOfMaterials --as alter -o json
gossms script object -p dev Sales.vSalesPerson --as drop-and-create --script-file vSalesPerson.sql
gossms script db -p dev Sales
gossms script db -p dev Sales --as drop-and-create --script-file Sales.sql

The text is the same Object Explorer's Script as ▸ ▸ File... writes — the same table and gosmo's script options (headers, schema-qualified names, IF NOT EXISTS guards, ANSI_PADDING, extended properties) — so TUI and CLI produce the same bytes. Nothing is run; both are class read. The object's script opens with USE [database]; a database's own script runs in master and has none. In text the script prints under === SCRIPT (n lines) ===; --script-file <path> writes it (UTF-8, LF, replaced atomically; an existing file needs --force; nothing is written when the script fails). --script-file is for scripts — --out-file is for rows.

--as table/view proc/function/trigger sequence/synonym db
create (default), drop, drop-and-create ✓ ✓ ✓ ✓
alter view ✓ — —
select / insert / update / delete ✓ — — —
execute — procedure — —

A verb the kind lacks is exit 2, listing the kind's verbs. Kinds: table, view, procedure (T-SQL or CLR), function, DML trigger, sequence, synonym. A constraint or index is not scripted alone — its table's CREATE carries it (the hint names the table); an object SQL Server ships is exit 2; database- and server-level principals are not covered (see Security reads).

Refusals: a definition the login cannot read (a module's text, or a table's default/check/computed expression — all NULL without VIEW DEFINITION) is exit 9: gosmo refuses rather than script DEFAULT followed by nothing. An encrypted module has no text to restate: create, alter and drop-and-create are UNSUPPORTED (exit 11); drop works. A table's DROP names its schema-bound dependents in a comment (without VIEW DEFINITION the view hides them and the DROP comes without the comment — exit 0).

Recipes

# map an unfamiliar database in four calls
gossms object list -p dev --type table,view -o json | jq -r '.resultSets[0].rows[] | .schema + "." + .name'
gossms object search -p dev "Order" --in column
gossms object describe -p dev Sales.SalesOrderHeader          # keys + indexes in one answer
gossms object dependencies -p dev Sales.SalesOrderDetail --direction used-by --depth 2

# read a module, then keep a copy under review
gossms object definition -p dev Reporting.usp_MonthlySales > baseline.sql
gossms script object -p dev Reporting.usp_MonthlySales --as drop-and-create --script-file change.sql

# index review before a maintenance window
gossms object indexes -p dev Sales.SalesOrderDetail --fragmentation limited -o json |
  jq -r '.resultSets[] | select(.name=="indexes") | .rows[] | [.name, .user_seeks, .user_scans, .avg_fragmentation_pct] | @tsv'
gossms object stats -p dev Sales.SalesOrderDetail -o json |
  jq -r '.resultSets[0].rows[] | select(.origin=="auto") | [.name, .last_updated, .modification_counter] | @tsv'

Clone this wiki locally