List of tables used in an SQL Query

Question

Is there a way how to get a list of tables used within an SQL query? Example : I have something like :

SELECT * FROM Table t JOIN OtherTable ON t.id=OtherTable.t_id

and I would expect to get

Table, OtherTable

Thanks

Answer 1

One solution using C# is to import Microsoft.SqlServer.TransactSql.ScriptDom (I found the dll at C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll) then do the following:

private List<string> GetTableNamesFromQueryString(string query)
{
    IList<ParseError> errors = new List<ParseError>();
    IList<TSqlParserToken> queryTokens;
    List<string> output = new List<string>(16);
    StringBuilder sb = new StringBuilder(128);
    TSql120Parser parser = new TSql120Parser(true);
    TSqlTokenType[] fromTokenTypes = new TSqlTokenType[2]
        {
            TSqlTokenType.From,
            TSqlTokenType.Join
        };
    TSqlTokenType[] identifierTokenTypes = new TSqlTokenType[2]
        {
            TSqlTokenType.Identifier,
            TSqlTokenType.QuotedIdentifier
        };

    using (System.IO.TextReader tReader = new System.IO.StringReader(query))
    {
        queryTokens = parser.GetTokenStream(tReader, out errors);
        if (errors.Count > 0) { return errors.Select(e=>"Error: " + e.Number + " Line: " + e.Line + " Column: " + e.Column + " Offset: " + e.Offset + " Message: " + e.Message).ToList(); }

        for (int i = 0; i < queryTokens.Count; i++)
        {
            if(fromTokenTypes.Contains(queryTokens[i].TokenType))
            {
                for (int j = i + 1; j < queryTokens.Count; j++)
                {
                    if (queryTokens[j].TokenType == TSqlTokenType.WhiteSpace) { continue; }
                    else if (identifierTokenTypes.Contains(queryTokens[j].TokenType))
                    {
                        sb.Clear();

                        GetQuotedIdentifier(queryTokens[j], sb);            //Change Identifiers to QuotedIdentifier (text only)

                        while (j + 2 < queryTokens.Count && queryTokens[j + 1].TokenType == TSqlTokenType.Dot && identifierTokenTypes.Contains(queryTokens[j + 2].TokenType))
                        {
                            sb.Append(queryTokens[j + 1].Text);
                            GetQuotedIdentifier(queryTokens[j + 2], sb);    //Change Identifiers to QuotedIdentifier (text only)

                            j += 2;
                        }

                        output.Add(sb.ToString());
                        break;              //exit the loop
                    }
                    else { break; }             //exit the loop if token is not a FROM, a JOIN, or white space.
                }

            }
        }

        return output.Distinct().OrderBy(tableName => tableName).ToList();
    }
}

private void GetQuotedIdentifier(TSqlParserToken token, StringBuilder sb)
{
    switch(token.TokenType)
    {
        case TSqlTokenType.Identifier: sb.Append('[').Append(token.Text).Append(']'); return;
        case TSqlTokenType.QuotedIdentifier: sb.Append(token.Text); return;
        default: throw new ArgumentException("Error: expected TokenType of token should be TSqlTokenType.Identifier or TSqlTokenType.QuotedIdentifier");
    }
}

I came up with this after trying to get this answer to work.

Answer 2

you can use this sql script right after your query. It will return a list of tables used in the last executed query:

   SELECT Field1, Field2 
   FROM Table t JOIN OtherTable ON t.id=OtherTable.t_id

  ;WITH vwQueryStats AS(
     SELECT 
      COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName
      ,execution_count
      ,s2.objectid
      ,(
         SELECT TOP 1 
            SUBSTRING(s2.TEXT,statement_start_offset / 2+1 
            ,( ( CASE WHEN statement_end_offset = -1
                THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
                ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement
            ,last_execution_time
         FROM sys.dm_exec_query_stats AS s1
         CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
    )
    SELECT TOP 1 * 
    INTO #lastQueryStats
    FROM vwQueryStats x
    WHERE sql_statement NOT like 'WITH vwQueryStats AS%'
    ORDER BY last_execution_time DESC

    SELECT
    TABLE_NAME
    FROM #lastQueryStats, INFORMATION_SCHEMA.TABLES tab 
    WHERE CHARINDEX( tab.TABLE_NAME, sql_statement) > 0


    DROP TABLE #lastQueryStats 

I've taken the query that retrieves the last executed query from this post and I modified it a bit to match with your example.

The output will be as you requested:

 Table
 OtherTable

Then if you want to have them comma separated you can do:

DECLARE @tableNames VARCHAR(MAX) 

SELECT @tableNames = COALESCE(@tableNames + ', ', '') + TABLE_NAME
FROM   #lastQueryStats, INFORMATION_SCHEMA.TABLES tab 
WHERE  CHARINDEX( tab.TABLE_NAME, sql_statement) > 0

SELECT @tableNames 

However you should be wary that in a 'usual' production or QA environment with thousands of query executed concurrently this mightn't work as another query could be executed in between your first query and the query that extracts info from db stats.

Hope it helps

Answer 3

Code below is based on Trisped's answer, but modified to work with fully qualified table names that omit a schema name, and a few cleanups/optimizations:

public class Parser
{
    public static List<string> GetTableNamesFromQueryString(string query)
    {
        var output = new List<string>();
        var sb = new StringBuilder();
        var parser = new TSql120Parser(true);

        var fromTokenTypes = new[]
        {
            TSqlTokenType.From,
            TSqlTokenType.Join
        };

        var identifierTokenTypes = new[]
        {
            TSqlTokenType.Identifier,
            TSqlTokenType.QuotedIdentifier
        };

        using (System.IO.TextReader tReader = new System.IO.StringReader(query))
        {
            IList<ParseError> errors;
            var queryTokens = parser.GetTokenStream(tReader, out errors);
            if (errors.Any())
            {
                return errors
                    .Select(e => string.Format("Error: {0}; Line: {1}; Column: {2}; Offset: {3};  Message: {4};", e.Number, e.Line, e.Column, e.Offset, e.Message))
                    .ToList();
            }

            for (var i = 0; i < queryTokens.Count; i++)
            {
                if (fromTokenTypes.Contains(queryTokens[i].TokenType))
                {
                    for (var j = i + 1; j < queryTokens.Count; j++)
                    {
                        if (queryTokens[j].TokenType == TSqlTokenType.WhiteSpace)
                        {
                            continue;
                        }

                        if (identifierTokenTypes.Contains(queryTokens[j].TokenType))
                        {
                            sb.Clear();
                            GetQuotedIdentifier(queryTokens[j], sb);

                            while (j + 2 < queryTokens.Count 
                                && queryTokens[j + 1].TokenType == TSqlTokenType.Dot 
                                && (queryTokens[j + 2].TokenType == TSqlTokenType.Dot || identifierTokenTypes.Contains(queryTokens[j + 2].TokenType)))
                            {
                                sb.Append(queryTokens[j + 1].Text);

                                if (queryTokens[j + 2].TokenType == TSqlTokenType.Dot)
                                {
                                    if (queryTokens[j - 1].TokenType == TSqlTokenType.Dot) 
                                        GetQuotedIdentifier(queryTokens[j + 1], sb);

                                    j++;

                                }
                                else
                                {
                                    GetQuotedIdentifier(queryTokens[j + 2], sb);
                                    j += 2;
                                }
                            }

                            output.Add(sb.ToString());
                        }
                        break;
                    }
                }
            }

            return output.Distinct().OrderBy(tableName => tableName).ToList();
        }
    }

    private static void GetQuotedIdentifier(TSqlParserToken token, StringBuilder sb)
    {
        switch (token.TokenType)
        {
            case TSqlTokenType.Identifier: 
                sb.Append('[').Append(token.Text).Append(']'); 
                break;
            case TSqlTokenType.QuotedIdentifier:
            case TSqlTokenType.Dot: 
                sb.Append(token.Text); 
                break;

            default: throw new ArgumentException("Error: expected TokenType of token should be TSqlTokenType.Dot, TSqlTokenType.Identifier, or TSqlTokenType.QuotedIdentifier");
        }
    }
}

Answer 4

One hackish way you could accomplish this would be to explicitly name the fields in your query and prefix them with the table name, e.g.

SELECT Field1 As "OtherTable.Field1",
       Field2 As "Table.Field2"
FROM Table t JOIN OtherTable ON t.id=OtherTable.t_id

Essentially, you're providing your own metadata in the query results. After your query returns, look at the column names and implement custom logic to split out the table names.

Answer 5

Trisped's solution works perfectly. I modified one line to ensure case insensitive and trim off the brackets.

OLD: output.Add(sb.ToString());

NEW: output.Add(sb.ToString().ToLower().Trim(new char[]{'[', ']'}));