Column provenance

SQLly remembers which table and column each result column really came from, so tools can act on the source instead of the label.

A result column's heading is presentation, not identity. SELECT p.Name AS PermissionName labels the column PermissionName, but that name does not exist as a column anywhere in your database - and T-SQL will not let you mention a SELECT alias in a WHERE clause at all. So when SQLly runs a statement, it also reads it: for every column in the grid it works out which table alias and underlying column produced that value, and keeps that answer attached to the result set.

That is what makes the grid actionable rather than decorative. Right-click a cell and ask for WHERE = this value, and SQLly does not paste the heading back at you; it writes the predicate against the real source column, correctly qualified and quoted for the engine the results came from.

Two questions, two answers

Provenance answers two different questions, and conflating them would produce SQL that does not compile. SQLly keeps them separate on purpose:

Worked example: an alias

The everyday case. Two joined tables, both with a Name column, both aliased in the select list:

SELECT
    rm.Name AS ModelName,
    p.Name  AS PermissionName
FROM Shared.ReferenceModelPermissions rmp
    INNER JOIN Shared.Permission p ON p.PermissionId = rmp.PermissionId
    INNER JOIN Shared.ReferenceModel rm ON rm.ReferenceModelId = rmp.ReferenceModelId

Hover the PermissionName header and SQLly tells you Source: p.Name from Shared.Permission p. Right-click a cell in it and choose WHERE = this value, and you get the predicate that actually runs:

-- what a label-based tool would write (and SQL Server would reject)
WHERE PermissionName = 'Export'

-- what SQLly writes
WHERE p.[Name] = 'Export'

Note the quoting: the alias stays bare because it needs no quoting, the column takes the engine's own delimiters, and the literal is rendered in that engine's dialect. The same resolution runs for every operator in the menu - <>, >=, LIKE, starts-with and ends-with, IN over a multi-cell selection, IS NULL - and for Match row, which resolves every column of the clicked row against the same single parse and joins them with AND.

Worked example: a derived column

Values that arrive through a subquery need both answers at once. The predicate must be written in the outer scope, where only sub exists; the tooltip should still tell you where the data really lives:

SELECT sub.y AS Z FROM (SELECT x AS y FROM T) sub

predicate  → sub.y = …      (valid outside; T.x would not be)
tooltip    → Source: T.x

The tracing handles the awkward spellings too. A star inside the subquery resolves through it when the body reads one table, and a derived table's column list renames the projections positionally:

SELECT sub.Name AS N FROM (SELECT * FROM Shared.Permission p) sub
  → Source: p.Name from Shared.Permission p

SELECT d.b AS Out FROM (SELECT p.Name, p.Id FROM P p) d (a, b)
  → b is the second projection: Source: p.Id from P p

Worked example: a CTE

A CTE behaves the same way: the outer statement sees a named relation, so predicates are written against that name, while the origin follows the body through to the base table. An explicit column list on the CTE renames positionally, exactly as on a derived table:

WITH x AS (SELECT p.Name AS a FROM Shared.Permission p)
SELECT x.a AS Alpha FROM x

predicate  → x.a = …
tooltip    → Source: p.Name from Shared.Permission p

WITH x (u, v) AS (SELECT p.Name, p.Id FROM P p) SELECT v FROM x
  → Source: p.Id from P p

Several CTEs in one statement resolve independently, so a two-CTE join attributes each column to its own base table.

Views, and how far the trail goes

Provenance reads the statement in front of it - the one sitting in your editor. A view in the FROM clause is therefore resolved as the object it is: predicates are written against the view (which is correct - the view has those columns), and the tooltip names the view and column. Following the trail past that point means reading the view's definition, which lives on the server rather than in your query.

Today that step is a deliberate, one-keystroke one: use DDL source navigation to open the view's definition, and provenance resolves inside that statement - aliases, derived tables, and CTEs included. Chaining it automatically, so a column in a view-over-view result reports its ultimate base table without you opening anything, is the natural extension of the same machinery and is what the cached schema model is being carried for.

When SQLly says nothing

Resolution is deliberately conservative, on the principle that a wrong predicate is worse than no predicate. When attribution is not certain, provenance returns nothing, the header gets no tooltip, and grid actions fall back to the plain result label. The cases:

• Computed expressions. COUNT(*) AS Total or p.First + p.Last AS FullName have no single source column, so they have no provenance.
• Unions and multi-statement scripts. With more than one top-level SELECT, there is no one statement to attribute against.
• Ambiguous stars. SELECT * over a join cannot be attributed without catalog data; p.* over a join can, because the qualifier says which table. Mixing a star with an explicit projection that shares its name is also refused - the clicked column could be either one.
• Bare columns over a join. SELECT Name FROM A a JOIN B b … keeps the column name but gains no qualifier. Over a single table it does gain one: the alias, or the schema-qualified table name when there is no alias.
• Anything nested deeper than one level. A derived table inside a derived table, a CTE reading another CTE, or a derived table whose body reads a CTE: the outer reference still resolves for predicates, but the origin is reported as unknown rather than guessed.
• Recursive or UNION CTE bodies. Opaque for origin purposes; the outer reference still works for predicates.

Duplicate headings are handled by position rather than by name: with p.Name, rm.Name the first grid column attributes to p and the second to rm. Quoted identifiers are compared dequoted and case-insensitively, so [Name], "Name", and `Name` all mean the same column - and a delimited name containing punctuation or leading with a digit is read whole rather than re-tokenized.

What it powers

One parse per statement feeds every surface that needs to talk about a result column in SQL terms:

• Header tooltips. Hover any column heading to see the source column, and the base table behind it when tracing adds something.
• WHERE from the grid. Every comparison, LIKE, IN, and null check in the cell context menu, written against the source column in the results' engine dialect - with a preview that is rendered from the same resolution it will use, so the preview and the inserted SQL cannot disagree.
• Match row. An AND of equality predicates for the whole clicked row, each column resolved individually.
• Filters, promoted to SQL. Filter the grid interactively, then Apply to Query / Add Active Filters to WHERE: the same resolver turns your column filters into real predicates instead of label comparisons.

SQLly

Why keep this in the model at all

Because a result set that knows its own lineage can be worked with, not just read. Carrying the extra metadata alongside the rows is what lets the grid build a correct filter from a cell you happened to notice, without you reconstructing the query in your head first - and it is the foundation for drilling from a value back to the object that produced it, and for generating multi-column predicates across joins and derived tables that would be tedious to write by hand. The metadata comes first; each of those tools is then a small, honest step on top of it rather than a guess.

Related: schema awareness supplies the relationships provenance leans on, and DDL source navigation opens the definition behind a view.