Список таблиц, используемых в запросе SQL
Есть ли способ, как получить список таблиц, используемых в запросе SQL? Пример: у меня есть что-то вроде:
SELECT * FROM Table t JOIN OtherTable ON t.id=OtherTable.t_id
и я ожидал бы получить
Table, OtherTable
Спасибо
5 ответов
Вы можете использовать этот сценарий SQL сразу после вашего запроса. Он вернет список таблиц, использованных в последнем выполненном запросе:
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
Я взял запрос, который извлекает последний выполненный запрос из этого поста, и немного изменил его, чтобы он соответствовал вашему примеру.
Вывод будет таким, как вы просили:
Table
OtherTable
Тогда, если вы хотите разделить их запятыми, вы можете сделать:
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
Однако следует опасаться, что в "обычной" рабочей среде или среде QA с тысячами запросов, выполняемых одновременно, это может не сработать, поскольку другой запрос может быть выполнен между вашим первым запросом и запросом, который извлекает информацию из статистики базы данных.
Надеюсь, поможет
Одним из решений с использованием C# является импорт Microsoft.SqlServer.TransactSql.ScriptDom (Я нашел длл в C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll) затем сделайте следующее:
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");
}
}
Я придумал это после попытки заставить этот ответ работать.
Приведенный ниже код основан на ответе Триспеда, но изменен для работы с полностью определенными именами таблиц, в которых не указано имя схемы, и внесены некоторые исправления / оптимизации:
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");
}
}
}
Решение Триспед работает отлично. Я изменил одну строку, чтобы обеспечить отсутствие учета регистра и обрезать скобки.
OLD: output.Add(sb.ToString());
NEW: output.Add(sb.ToString().ToLower().Trim(new char[]{'[', ']'}));
Один из хакерских способов сделать это - явно указать имена полей в вашем запросе и поставить перед ними префикс имени таблицы, например:
SELECT Field1 As "OtherTable.Field1",
Field2 As "Table.Field2"
FROM Table t JOIN OtherTable ON t.id=OtherTable.t_id
По сути, вы предоставляете свои собственные метаданные в результатах запроса. После того, как ваш запрос вернется, посмотрите на имена столбцов и реализуйте пользовательскую логику, чтобы разделить имена таблиц.