ScriptDom parsing of SELECT with alias

Question

I'm looking to use ScriptDom to parse T-SQL statement and ensure that at least two columns are specified. I found a way to extract the column names and can properly do my validation from that. However I also want to validate if the column was aliased and am not sure how to check that.

I want to ensure that the select statement specifies both MCA and MOC columns.

// this should pass
var sql = "SELECT MCA, MOC
FROM myTable";

// this should also pass
var sql = "SELECT some_col as MCA, some_other_col as MOC
FROM myTable";

_parser = new TSql120Parser(false);
var reader = new StringReader(sql);
var statements = parser.ParseStatementList(reader, out _errors);
var firstStatement = statements[0];
var select = firstStatement as SelectStatement;

var querySpec = statement.QueryExpression as QuerySpecification;
var columns = querySpec.SelectElements;
foreach(var column in columns) {
  if (column is SelectScalarExpression) {
    var expression = (column as SelectScalarExpression).Expression;
    if (expression is ColumnReferenceExpression) {
      var columnExpression = expression as ColumnReferenceExpression;
      if (identifier.Identifiers.Count == 1)
      {
        var columnName = identifier.Identifiers.First().Value;
        // I can validate from this extracted column name
      }
    }
  }
}

hi. ever got an answer to this? – Yisroel M. Olewski Dec 23 '19 at 12:40 — Yisroel M. Olewski, Dec 23 '19 at 12:40

score 2 · Answer 1 · answered Jun 16 '20 at 19:29

SelectScalarExpression has a ColumnName property, which is at the same level as ColumnReferenceExpression. If there is no alias on the column, this will be null.

Your loop could contain something like this:

if (column is SelectScalarExpression)
{
    var columnAlias = (column as SelectScalarExpression).ColumnName?.Value;

    if (columnAlias == null)
    {
        // evaluate the ColumnReferenceExpression here
    }
}

ScriptDom parsing of SELECT with alias

1 Answers1