Active Query Builder support area

Analyze SQL query: Get common information (used objects, fields, output columns)

Last modified:


Database objects usage and other information about the currently loaded SQL query are instantly available through the QueryBuilder.QueryStatistics set of collections.

  1. Used database objects: QueryBuilder.QueryStatistics.UsedDatabaseObjects collection.

    Each item of this collection has a link to the MetadataObject object to retrieve detailed information about this database object. Please note that this link might be set to null if such object can not be found in the Metadata Container.

  2. Used database object fields: QueryBuilder.QueryStatistics.UsedDatabaseObjectColumns collection.

    Each item of this collection has a name and type of the field. It also has links to the MetadataField and MetadataObject objects to retrieve detailed information about this field and the database object to which it belongs. The Selected property indicates if this field is used in some of the output columns in the query.

    Please note that this collection does not contain fields that were used in the query without table alias prefix. This because it is impossible to determine the correspondence of this field to a table uniquely.

  3. Output query columns: QueryBuilder.QueryStatistics.OutputColumns collection.

    This collection lists output columns of the query. Each item of this collection has the Expression and ExpressionAlias properties to retrieve SQL expression, and it's alias (if defined). If SQL expression is a simple database object field, additional information for it is available: field type, object name, links to MetadataField and MetadataObject objects, etc.

Such information is available for each element in the query object model. The following code snippet gives information about fields of which each query output column consist:

private void button1_Click(object sender, EventArgs e)
{
    var outputColumn = queryBuilder1.QueryStatistics.OutputColumns[0];
    var queryColumnListItem = outputColumn.QueryColumnListItem;
    var expressionStatistics = queryColumnListItem.QueryStatistics;

    var report = new StringBuilder("Used fields:\n");
    foreach (var usedField in expressionStatistics.UsedDatabaseObjectFields)
    {
        var metadataObject = usedField.MetadataObject;
        var metadataField = metadataObject?.FindItem(usedField.FieldName);

        if (metadataObject == null)
        {
            report.Append("field from unknown object: ").AppendLine(usedField.FullName.QualifiedName);
            continue;
        }

        report.Append("object: ").Append(usedField.MetadataObject.NameFull).Append(", ");

        if (metadataField == null)
        {
            report.Append("unknown field: ").AppendLine(usedField.FieldName.QualifiedName);
            continue;
        }

        report.Append("field: ").AppendLine(metadataField.Name);
    }

    MessageBox.Show(report.ToString());
}

And this snipped lets you find database objects used in each subquery across a complex SQL query:

// assign sample query
queryBuilder.SQL = "Select o.SalesOrderID, c.CompanyName " +
                   "From (select SalesOrderID, CustomerID from live.AdventureWorks.Sales.SalesOrderHeader soh) o " +
                   " Inner Join xml.Northwind.dbo.Customers c On o.CustomerID = c.CustomerID "+
                   " cross join bad_schema.bad_name bn";

var result = new StringBuilder();

// collect all subQueries
var subQueries = queryBuilder.SQLQuery.QueryRoot.GetChildrenRecursive<SubQuery>(true); ;
// process main query also
subQueries.Insert(0, queryBuilder.SQLQuery.QueryRoot);

// OR collect unionSubQueries (single SELECT expressions)
//var subQueries = queryBuilder.SQLQuery.QueryRoot.GetChildrenRecursive<UnionSubQuery>(true);

foreach (var subQuery in subQueries)
{
    result.AppendLine();
    result.AppendLine("subQuery: " +subQuery.SQL);
    // collect all dataSources in this subQuery
    var dataSources = subQuery.GetChildrenRecursive<DataSourceObject>(false);
    
    foreach (var dataSource in dataSources)
    {
        result.AppendLine(dataSource.NameInQuery);
        
        var metadataObject = dataSource.MetadataObject;
        
        // metadataObject will be null in 2 cases:
        // 1. dataSource is CTE reference
        if (dataSource.SubQueryCTE != null)
        {
            result.AppendLine("\tCTE reference");
            continue;
        }
        
        // 2. no object with such name in MetadataContainer
        if (metadataObject == null)
        {
            using (var fullName = new SQLQualifiedName(queryBuilder.SQLContext))
            {
                fullName.Assign(dataSource.DatabaseObject);
                result.AppendLine("\tno such object in DB: " + fullName.GetSQL(queryBuilder.SQLGenerationOptions));
            }
            continue;
        }
        
        result.AppendLine("\tobject name:" + metadataObject.GetQualifiedNameSQL(null, queryBuilder.SQLGenerationOptions));
    }
}

MessageBox.Show(result.ToString());

Is this article helpful for you?