Repository navigation
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.
-
Where they look. An
objectorscriptcommand reads the connection's database — the profile's, or-d/--database— and the header'sDATABASEnames 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 areschema.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_ERRORlisting them asschema.name; none isNOT_FOUNDwith up to five objects whose names contain it. It never assumesdbo. Names compare by the database's catalog collation — in a_CS_one,sales.customeris notSales.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.
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-systemOne 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 |
gossms object search -p dev "Customer"
gossms object search -p dev "CustomerID" --in column
gossms object search -p dev "GETDATE()" --in definition -o jsonCase-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.
gossms object describe -p dev Sales.SalesOrderHeader
gossms object describe -p dev Sales.SalesOrderHeader -o json
gossms object describe -p dev dbo.uspGetBillOfMaterialsA 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.
gossms object definition -p dev Sales.vSalesPerson
gossms object definition -p dev dbo.uspGetBillOfMaterials -o jsonThe 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.
gossms object dependencies -p dev Reporting.usp_MonthlySales --direction uses
gossms object dependencies -p dev Sales.SalesOrderDetail --direction used-by --depth 3 -o jsonOne 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 3is "what breaks if I change it". -
--depth1–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.
gossms object indexes -p dev Sales.SalesOrderDetail
gossms object indexes -p dev Sales.SalesOrderDetail --fragmentation limited -o jsonOne 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.
gossms object stats -p dev Sales.SalesOrderDetail
gossms object stats -p dev Sales.SalesOrderDetail -o jsonOne 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.
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.sqlThe 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).
# 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'- Getting connected
- Browsing and editing
- Diagnosing and tuning
- Server features