A SqlScriptDOM visitor can be used to easily find FOR JSON clauses within a T-SQL script. This will facilitate creating a new script without FOR JSON for use by sp_describe_first_result_set to retrieve result set meta data that would otherwise be obscured by the json result.
Below is a .NET console application example that uses a visitor to find FOR JSON clauses in a T-SQL script, creates a new script without those clauses, and finally invokes sp_describe_first_result_set to load result set meta data into a data table.
See this article for more information on the T-SQL Script DOM and Visitor.
using Microsoft.Data.SqlClient;
using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Data;
using System.Diagnostics;
string tSqlScriptWithForJson = "SELECT * FROM sys.tables FOR JSON PATH;";
string connectionString = "Data Source=.;Integrated Security=SSPI;Initial Catalog=tempdb;TrustServerCertificate=true";
var example = new VisitorExample();
//use visitor to get script without FOR JSON clauses
string tSqlScriptWithoutForJson = example.GetScriptWithoutForJson(tSqlScriptWithForJson);
Debug.WriteLine($"Original script: {tSqlScriptWithForJson}");
Debug.WriteLine($"Original script: {tSqlScriptWithoutForJson}");
//execute sp_describe_first_result_set without JSON FOR cleause to load result set meta data into data table
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("sp_describe_first_result_set", connection) { CommandType = System.Data.CommandType.StoredProcedure })
{
var metaData = new DataTable();
command.Parameters.Add("tsql", System.Data.SqlDbType.NVarChar, -1).Value = tSqlScriptWithoutForJson;
connection.Open();
metaData.Load(command.ExecuteReader());
}
//A visitor class derives from TSqlConcreteFragmentVisitor or TSqlFragmentVisitor.
//The overloaded Visit method is invoked whenever a fragment of the specified type is visited after the Accept method call.
public class VisitorExample : TSqlConcreteFragmentVisitor
{
// visited JSON FOR clause fragments
public List<JsonForClause> jsonForClauses = new List<JsonForClause>();
// get T-SQL script with JSON FOR clauses removed
public string GetScriptWithoutForJson(
string tSqlScript
)
{
var parser = new TSql170Parser(true, SqlEngineType.All);
IList<ParseError> parseErrors = new List<ParseError>();
//create TextReader with script for parsing
TextReader stringReader = new StringReader(tSqlScript);
//parse script to get fragments and tokens
var tSqlScriptFragment = (TSqlScript)parser.Parse(stringReader, out parseErrors);
if (parseErrors.Count > 0)
{
//throw custom exception with all parsing errors
throw new TSqlParseException(tSqlScriptFragment, parseErrors);
}
//invoke visitor to find JSON FOR clause fragments
tSqlScriptFragment.Accept(this);
//return script without JSON FOR clauses
return getScriptWithoutJsonForClauses(tSqlScriptFragment);
}
public override void Visit(JsonForClause sqlFragment)
{
//add FOR JSON fragments to list - these will be removed in the final script
jsonForClauses.Add(sqlFragment);
}
private string getScriptWithoutJsonForClauses(TSqlFragment tSqlScriptFragment)
{
var scriptWithoutJsonForClauses = new StringWriter();
for (int tokenIndex = tSqlScriptFragment.FirstTokenIndex; tokenIndex <= tSqlScriptFragment.LastTokenIndex; ++tokenIndex)
{
if (!isTokenInForJsonClause(tokenIndex))
{
//keep original script text if not in a JSON FOR clause, otherwise ignore
scriptWithoutJsonForClauses.Write(tSqlScriptFragment.ScriptTokenStream[tokenIndex].Text);
}
}
return scriptWithoutJsonForClauses.ToString();
}
//return true if token is in a FOR JSON clause
private bool isTokenInForJsonClause(int tokenIndex)
{
foreach (var jsonForClauseFragment in jsonForClauses)
{
if (tokenIndex >= jsonForClauseFragment.FirstTokenIndex && tokenIndex <= jsonForClauseFragment.LastTokenIndex)
{
return true;
}
}
return false;
}
}
//custom class for parser exceptions
public class TSqlParseException : Exception
{
private TSqlScript tSqlScriptFragment;
private IList<ParseError> parseErrors;
public TSqlScript TSqlScriptFragment { get => tSqlScriptFragment; }
public IList<ParseError> ParseErrors { get => parseErrors; }
public TSqlParseException(TSqlScript tSqlScriptFragment, IList<ParseError> parseErrors) : base($"{parseErrors.Count} error{(parseErrors.Count == 1 ? "" : "s")} parsing T-SQL script.")
{
this.tSqlScriptFragment = tSqlScriptFragment;
this.parseErrors = parseErrors;
}
}
sp_describe_first_result_setdoesn't work with? Or is it your own parser that's the problem? How isSqlScriptDOMrelevant? Please show an example query, along with what you are trying to achieve, and what you are currently doing that has the issue.