从查询中获取表模式 [英] Getting table schema from a query
问题描述
根据 MSDN，SqlDataReader.GetSchemaTable 返回执行查询的列元数据.我想知道是否有类似的方法可以为给定查询提供表元数据?我的意思是涉及哪些表以及它有哪些别名.
As per MSDN, SqlDataReader.GetSchemaTable returns column metadata for the query executed. I am wondering is there a similar method that will give table metadata for the given query? I mean what tables are involved and what aliases it has got.
在我的应用程序中，我得到了查询，我需要以编程方式附加 where 子句.使用 GetSchemaTable()，我可以获得列元数据和它所属的表.但是即使表有别名，它仍然返回真实的表名.有没有办法获取该表的别名?
In my application, I get the query and I need to append the where clause programically. Using GetSchemaTable(), I can get the column metadata and the table it belongs to. But even though table has aliases, it still return the real table name. Is there a way to get the aliase name for that table?
以下代码显示获取列元数据.
Following code shows getting the column metadata.
const string connectionString = "your_connection_string";
string sql = "select c.id as s,c.firstname from contact as c";
using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable schema = reader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
foreach (DataColumn column in schema.Columns)
{
Console.WriteLine(column.ColumnName + " = " + row[column]);
}
Console.WriteLine("----------------------------------------");
}
Console.Read();
}
这将正确地为我提供列的详细信息.但是当我看到列 Id 的 BaseTableName 时，它给出的是 contact 而不是别名 c.有没有办法从上述查询中获取表架构和别名?
This will give me details of columns correctly. But when I see BaseTableName for column Id, it is giving contact rather than the alias name c. Is there any way to get the table schema and aliases from a query like the above?
任何帮助都会很棒！
编辑
虽然我可以使用 Rob 建议的执行计划，但我会很感激任何其他简单的方法.
While I could use the execution plan suggested by Rob, I'd appreciate any alternative simple approaches.
tomekszpakowicz 回答问题
您是(或您的应用程序)来源吗有问题的查询?在这种情况下你应该知道别名.
Are you (or your application) source of the query in question? In that case you should know the aliases.
我不是查询的作者.我们有一个系统，用户可以在其中输入查询.我们使用我上面解释的方法从中构建列.这些细节将被持久化，另一个用户可以使用它，比如添加新的标准等.所以我们需要根据我们拥有的信息动态地构建 SQL.所以当一个列被别名并且我们没有得到别名时，那么构造的 where 子句将是无效的.
I am not the author of queries. We have a system where users can enter the query. We build columns out of it using the method I explained above. These details will be persisted and another user can use this like adding new criteria etc. So we need to build the SQL dynamically from the information we have. So when a column is aliased and we are not getting alias name, then the where clause constructed will be invalid.
谢谢
推荐答案
简答
这行不通.根据设计，您不能从结果模式中获取表别名.而且您不能依赖能够从查询执行计划中获取它们.
This won't work. You cannot, by design, get table aliases from result schema. And you cannot rely on being able to get them from query execution plan.
长答案
当您获得 SQL 查询的结果时，该查询已经被解析、验证、优化、编译成某种内部表示并执行.别名是查询源代码"的一部分，通常在第 1 步和第 2 步左右丢失.
When you get result of a SQL query, the query has already been parsed, validated, optimized, compiled into some internal representation and executed. Aliases are part of query's "source code" and are usually lost somewhere around step 1 and 2.
执行查询后，唯一可以视为表的内容是 a) 真实的物理表和 b) 返回的数据，视为单个匿名表.两者之间的一切都可以转换或完全优化.
After query is executed the only things that can be seen as tables are a) real physical tables and b) returned data seen as single anonymous table. Everything between can be transformed or completely optimized out.
如果要求 DBMS 保留别名，那么优化复杂查询实际上是不可能的.
If DBMSes were required to retain aliases it would be practically impossible to optimize complex queries.
可能的解决方案
我建议重述一个问题:
您(或您的应用程序)是相关查询的来源吗?在这种情况下，您应该知道别名.
Are you (or your application) source of the query in question? In that case you should know the aliases.
如果您收到其他人提供的查询...嗯...这取决于您为什么要添加 where 原因.
If you get queries provided by someone else... Well... That depends on why are you adding where causes.
在最坏的情况下，您必须自己解析查询.
In the worst case, you'll have to parse queries yourself.
在最好的情况下，您可以让他们访问视图而不是真实表，并将 where 子句放在视图中.
In the best case, you could give them access to views instead of real tables and put where clauses in the views.
<小时>
简单而丑陋的解决方案
如果我正确理解您的要求:
If I understand your requirements correctly:
用户 A 在您的程序中输入查询.
User A enters query into your program.
用户 B 可以运行它(但不能编辑它)并查看返回的数据.此外，她可以使用您提供的某种小部件根据返回的列添加过滤器.
User B can run it (but cannot edit it) and sees returned data. Additionally she can add filters based on returned columns using some kind of widget provided by you.
您不想在应用程序中应用过滤器，而是将它们添加到查询中，以避免从数据库中获取不必要的数据.
You don't want to apply filter inside application but instead add them to the query, in order to avoid fetching unnecessary data from database.
在这种情况下:
当一个编辑查询尝试运行它并为返回的列收集元数据.如果
ColumnName不是唯一的，请向作者投诉.使用查询存储元数据.
When A edits query try to run it and gather metadata for returned columns. If
ColumnNames are not unique, complain to the author. Store metadata with query.
当 B 添加过滤器(基于查询元数据)时，存储两个列名和条件.
When B adds filter (based on query metadata), store both columns names and conditions.
执行时:
检查过滤器列是否仍然有效(A 可能已更改查询).如果不删除无效过滤器和/或通知 B.
Check if filter columns are still valid (A might have changed query). If not remove invalid filters and/or inform B.
像这样执行查询:
select *
from ({query entered by A}) x
where x.Column1 op1 Value1
and x.Column2 op2 Value2
如果您想优雅地处理数据库架构更改，您需要添加一些额外的检查以确保元数据与查询真正返回的内容一致.
If you want to gracefully handle database schema changes you need to add some additional checks to make sure metadata is consistent with what query really returns.
安全说明
您的程序会将用户 A 编写的查询直接传递给数据库.使用权限不超过 A 的数据库权限的数据库连接来执行此操作至关重要.否则，您要求的是基于 SQL 注入的漏洞利用.
Your program is going to pass a query written by user A straight to database. It is crucial that you do it using database connection with permissions which do not exceed A's database permissions. Otherwise you are asking for SQL injection based exploits.
推论
如果用户 A 出于安全原因不能直接访问数据库，则不能使用上述解决方案.
If user A doesn't have direct access to the database out of security reasons, you cannot use above solution.
在这种情况下，确保它安全的唯一方法是确保您的应用程序理解 100% 的查询，这意味着在您的程序中解析它并只允许您认为安全的操作.
In that case the only way to make it safe is to make sure your application understands 100% of the query which means parsing it in your program and allowing only operations you consider safe.
这篇关于从查询中获取表模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
