sql-servercommand-line-interfacesqlcmdoutput-formatting

How can I both export execution plan XML and get column names in tables?


I need to get both the full text of an actual execution plan and my full table of results with column names from sqlcmd. My first requirement forces to me pass in -y 0 as documented here. However, this appears to disable column names appearing in my table's output. I cannot find any reference to this in the documentation. So how can I both pass in -y 0 and get column names?

Alternatives to sqlcmd are not an option. I'm using sqlcmd because, as far as I can tell, Emacs demands that I must and Emacs is the only IDE that I know of for Microsoft SQL Server on Linux. My version is

~$: sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 18.2.0001.1 Linux
Copyright (C) 2017 Microsoft Corporation. All rights reserved.


Solution

  • Don't know if there are any docs about this, but I recently had the pleasure of reading through sqlcmd-go source code, where we can see how the headers are printed:

    https://github.com/microsoft/go-sqlcmd/blob/main/pkg/sqlcmd/format.go#L150

    if f.vars.RowsBetweenHeaders() > -1 && f.format == "horizontal" && !f.xml {
            f.printColumnHeadings()
        }
    

    Looking up RowsBetweenHeaders definition it says:

    https://github.com/microsoft/go-sqlcmd/blob/main/pkg/sqlcmd/variables.go#L164

    // RowsBetweenHeaders is the value of SQLCMDHEADERS variable.
    // When MaxVarColumnWidth() is 0, it returns -1
    func (v Variables) RowsBetweenHeaders() int64 {
        if v.MaxVarColumnWidth() == 0 {
            return -1
        }
        h := mustValue(v[SQLCMDHEADERS])
        return h
    }
    

    So it would seem that these two settings are in antithesis with each other.

    You might have some luck adding an issue or a pull request to add another option which prints the headers anyway, something like: --always-print-long-values-as-if-max-width-is-0-but-headers-are-still-ok-to-print. I might even upvote that