Query Pilot Objects

The Objects tab is the Query Pilot metadata browser. It follows the active query tab's target scope and lets the user choose one target as the metadata source.

The active query tab can still run against many servers. The object browser uses one selected server so database and object metadata are readable and deterministic.

Objects are grouped by server, then top-level Databases and Security / Logins branches. Database objects are grouped by database, schema, object type, and object name. Tables, views, procedures, functions, and synonyms are shown under the schema that owns them.

Database grouping is configurable from Settings > Tree or from the database branch context menu. Flat is the default and shows databases directly under the Databases branch. System/User separates master, model, msdb, and tempdb from user databases. Folders uses local manual folders created from the Databases branch context menu; right-click a database and use the Move to folder picker, or drag the database onto a manual folder. Drag a filed database back onto the Databases branch to remove its manual folder assignment. Tags groups databases by database-level Query Pilot tags, with untagged databases collected under an untagged folder.

Database folders are a presentation overlay. They do not create SQL Server folders, rename databases, or change where the database lives. Manual database folders are local workstation settings in the first pass. Tag grouping uses the same system-wide or local tag assignment model used elsewhere in the object browser.

Use Smart folder from the Databases branch context menu to file databases from a rule preview. Folder rules are for stable categories: database name pattern, tags, system/user type, Availability Group name, Availability Group role, app/role, SLA, owner, recovery model, and database status.

Use Sort by directly from the Databases branch context menu for ordering. Numeric and time-based values do not create folders; they are sort choices instead, including size, free space, used/free percent, last backup, and last CheckDB. Read and write I/O are listed as planned sort actions for a later repo-inventory hookup. Row count is intentionally not a folder or sort field because a database can contain many tables with independent row counts.

Smart folder naming has two modes. Use one folder moves the checked preview rows into one destination folder. Create folders from values creates or reuses one folder per matched category value, such as one folder per AG name or one folder per SLA, and leaves databases with blank category values alone.

Smart folder move behavior has three modes. Move matches into folder moves checked databases into the destination folder and replaces any existing manual folder assignment for those databases. Only move unfiled matches moves only checked databases that do not already have a manual folder. Make folder match selection syncs the destination folder to the checked preview rows: checked databases move in, and unchecked databases already in that folder move back to Unfiled.

Server logins are shown under the server-level Security / Logins folder. They can be viewed flat or grouped as SQL logins, Windows users, Windows groups, Azure/external logins, and built-in/system logins. The folder uses repository inventory from Collector.Logins; it does not need to walk every database to show the login list.

Login nodes use type-specific icons and status badges. Disabled and locked logins are marked directly in the tree. Server-role badges use the same glyphs as the Login Security Center role pills; if a login has more than one server role, Query Pilot collapses them into one role badge with a tooltip listing every role. Query Pilot also starts a lazy estate-wide SID drift check from repository login inventory. When the same login name has more than one SID across servers, the login receives a SID warning badge with a tooltip listing the affected servers.

Right-click a login, login group, or the Security / Logins folder and choose Clone/script logins to open the clone preview window. The left side lets the user choose one or many logins and one or many destination servers. The right side has separate Create statements and Drop statements tabs. Query Pilot reads Clone.LoginScripts, puts Stmt values on the Create tab, and puts NegativeStmt values on the Drop tab. A SQL icon at the bottom shows the query summary so the operator can tell which repo rows produced the scripts.

Clone preview options can include only login/server statements, only database permissions, or both. They can also include or strip SQL login password hashes, include or strip SIDs, and keep or remove the IF NOT EXISTS / IF EXISTS checks. If password hashes are removed, Query Pilot leaves a password placeholder in SQL login create statements so the generated script stays obvious and editable instead of silently producing an invalid login.

The Scheduling tab queues the selected create or drop script into Deploy.Code using the selected target servers. It exposes the Deploy.Code run window, retry count, retry frequency, category, and optional deploy-failure alert settings. The normal Deploy job owns execution after the rows are queued.

Tables can be expanded to inspect their useful child metadata without opening a separate dialog. Query Pilot shows stable folders for columns, keys, indexes, foreign keys, and triggers. Folders remain visible even when the count is zero so context-menu actions have a predictable home. Child rows are loaded lazily when the object node is expanded, which keeps the first tree load fast on large servers.

Procedures and functions can be expanded to show parameters. Parameter rows include the SQL type and output direction when SQL Server exposes it. Column and parameter rows render like compact DDL: name, type, nullability, and extras are colored separately so the metadata is easier to scan than a single text string. Detail rows are informational only: double-click, script, edit data, and dependency actions still apply to the real database object node above them.

Object names load first. Query Pilot paints the tree from a lightweight shell list, then enriches table and routine metadata in the background. Until enrichment finishes, count badges that would otherwise be unknown are hidden instead of showing fake zeroes. Full child rows load only for expanded objects and are cached for the session. Use Refresh to reload object metadata and clear stale detail state from SQL Server.

Metadata badges beside object names show important table and routine signals without expanding the node. Defaults include heap or clustered status, clustered GUID warning, primary key, column count, index count, and procedure/function parameter count. Additional badges for foreign keys, references, triggers, temporal tables, memory-optimized tables, and partitioned tables can be enabled from Query Pilot settings.

Badge colors are semantic and can be changed in Settings > Tree. Each badge also has a shape setting. Most badges default to pills, while the clustered GUID warning defaults to a caution triangle.

Reference badges beside object names show how many distinct database objects depend on that object. Query Pilot loads dependencies lazily in parallel, updates badges as each database finishes, and caches each database's dependency edges for the session. Click a badge to list the referencing views, procedures, functions, triggers, and synonyms. Click a dependent object to open its definition at the first matching reference.

The dependency catalog is scoped to the object browser's selected server. Cross-database references on that server are included when SQL Server records their names. Dynamic SQL is not counted because SQL Server does not expose dependencies hidden inside constructed strings. The connected login also needs metadata visibility for the objects that should appear.

Object tags let DBAs annotate catalog objects directly in the tree. Right-click a database or object and choose Add tag, or click the compact #n tag affordance beside an already tagged node, to assign a tag color, scope, shape, and note.

Tag scope controls where the assignment lives:

• System-wide stores the tag definition and assignment in the repository database under the Tag schema so the rest of the team can see it.
• Local only stores the assignment in QueryRunnerObjectTags.json on the current workstation.

Use Settings > Tree > Manage tags to manage system-wide object tags before applying them. The manager can create tags, rename tags, edit display text, change color and shape, update the team-facing description, show active object assignment counts, and soft-delete tags with their assignments.

The object tree loads tags after the first metadata view is painted. This keeps normal browsing responsive while still filling in tag counts as soon as local JSON and repository tag metadata are available. Support bundles include tag names and target metadata for local tags, but omit tag notes.

Double-click a table to open a DevExpress editable grid as the main content of a new Query Pilot tab. The grid supports grouping, filtering, sorting, editable cells, export, and column chooser behavior. Edits are local in the current first pass.

Right-click a table, view, stored procedure, or function to insert SQL into the active editor without typing the object name. Insert object name inserts [schema].[object]. Tables and views expose Insert SELECT *, which inserts SELECT * FROM [schema].[object];. Procedures and functions expose Insert call: procedures declare parameter variables and use EXEC [schema].[procedure] with named parameter assignments, scalar functions declare argument variables and use SELECT [schema].[function](...), and table-valued functions declare argument variables and use SELECT * FROM [schema].[function](...);. Procedure output parameters are marked OUTPUT in the EXEC call. Insert SELECT all columns is table-only and lazy-loads the table's column list in catalog order.

Table context menus also include DML templates. Insert VALUES declares one row of variables and skips computed and identity columns. Insert multi VALUES uses literal placeholders such as empty strings, sample numbers, date literals, and NULL where the type is not obvious, so the stub stays compact. Insert SELECT creates an INSERT INTO ... SELECT ... FROM ... AS src starter using insertable columns. Update declares variables, sets non-key mutable columns, and uses primary-key columns for the WHERE clause when the key is available. Delete uses the same primary-key WHERE behavior. When no key is available, Query Pilot inserts a guarded WHERE /* TODO: add predicate */ 1 = 0 placeholder.

Table and column context menus expose one Alter table entry. It opens the alter-table designer with the table's columns, selected-column rename input, inline add-column controls, up/down reorder controls, style chooser, options panel, and a live generated SQL preview. Reordering uses a conservative replacement-table rebuild scaffold because SQL Server cannot reorder columns with a simple metadata operation. Dragging a column onto another column in the object tree opens the same designer with the proposed position applied. Generated reorder SQL is straight T-SQL, rolls back by default, and includes a commented -- COMMIT TRAN; marker immediately before the rollback so the operator has to deliberately flip the final behavior after review. Generated alter-table SQL can be copied or opened in Query Pilot, so execution still goes through normal Query Pilot checks, targets, policy, and run logging.

Objects can also be dragged from the object tree into the editor:

• Plain drag inserts [schema].[object].
• Shift+drag inserts SELECT * for tables and views, or the runnable call starter for routines.
• Ctrl+Shift+drag inserts SELECT with every column named explicitly for tables; other object types use the runnable starter statement.
• Drag a single column detail row to insert [ColumnName].
• Drag the Columns folder to insert every visible column as a comma-separated list.

When Require INSERT column lists is enabled in settings, syntax check also flags INSERT table VALUES (...) statements that do not name target columns. It also checks obvious count mismatches between an INSERT column list and its VALUES rows or direct INSERT ... SELECT projection.

Double-click a code object, such as a view, procedure, or function, to open its SQL definition in the editor.

Related: Query Pilot.

Documentation revision: B6D308D2AF98F7CF