从查询中获取表架构

Question

根据MSDN，SqlDataReader.GetSchemaTable返回已执行查询的列元数据。我想知道是否有类似的方法将为给定查询提供表元数据？我的意思是涉及哪些表以及它具有什么别名。

在我的应用程序中，我得到了查询，并且需要以where编程方式附加该子句。使用GetSchemaTable()，我可以获取列元数据及其所属的表。但是，即使表具有别名，它仍然会返回真实的表名。有没有办法获取该表的别名？

以下代码显示如何获取列元数据。

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();
}

这将为我正确提供列的详细信息。但是当我看到BaseTableName列时Id，它给出的contact是别名而不是别名c。有没有办法从上述查询中获取表架构和别名？

任何帮助将是巨大的！

编辑

尽管我可以使用Rob提出的执行计划，但我会感激任何其他简单的方法。

通过tomekszpakowicz回答问题

您（或您的应用程序）是否在查询中？在这种情况下，您应该知道别名。

我不是查询的作者。我们有一个用户可以输入查询的系统。我们使用上面说明的方法在其中构建列。这些细节将保留下来，其他用户可以使用它，例如添加新条件等。因此，我们需要根据已有的信息动态构建SQL。因此，当为列添加别名并且我们没有获取别名时，构造的where子句将无效。

谢谢

Answer 1

简短答案

这行不通。从设计上讲，您不能从结果模式中获取表别名。而且，您不能依靠能够从查询执行计划中获取它们。

长答案

当您获得SQL查询的结果时，该查询已经被解析，验证，优化，编译成某种内部表示并得以执行。别名是查询“源代码”的一部分，通常在步骤1和步骤2左右丢失。

执行查询后，可以看作表的唯一内容是a）实际物理表和b）返回的数据被视为单个匿名表。两者之间的所有内容都可以转换或完全优化。

如果要求DBMS保留别名，那么实际上不可能优化复杂的查询。

可能的解决方案

我建议重申一个问题：

1. 您（或您的应用程序）是否在查询中？在这种情况下，您应该知道别名。

2. 如果您收到其他人提供的查询…那么…这取决于您为什么要在何处添加原因。

   • 在最坏的情况下，您必须自己解析查询。

   • 在最佳情况下，您可以授予他们访问视图的权限，而不是实际表的访问权限，然后在视图中放置where子句。

简单丑陋的解决方案

如果我正确理解您的要求：

• 用户A在您的程序中输入查询。

• 用户B可以运行它（但不能编辑它）并查看返回的数据。另外，她可以使用您提供的某种小部件根据返回的列添加过滤器。

• 您不想在应用程序内部应用过滤器，而是将其添加到查询中，以避免从数据库中获取不必要的数据。

在这种情况下：

• 当A编辑查询尝试运行它并收集返回列的元数据。如果ColumnNames不是唯一的，请向作者投诉。使用查询存储元数据。

• 当B添加过滤器时（基于查询元数据），请同时存储列名和条件。

• 执行时：

  • 检查过滤器列是否仍然有效（A可能已更改查询）。如果没有，请删除无效的过滤器和/或通知B。

  • 以如下方式执行查询：

         select *
    

    from ({query entered by A}) x
    where x.Column1 op1 Value1
    and x.Column2 op2 Value2
    
    

如果要妥善处理数据库架构更改，则需要添加一些其他检查，以确保元数据与查询真正返回的内容一致。

安全须知

您的程序将直接将用户A编写的查询传递给数据库。使用具有不超过A的数据库权限的权限的数据库连接来执行此操作至关重要。否则，您将要求基于SQL注入的漏洞利用。

推论

如果出于安全原因用户A无法直接访问数据库，则不能使用上述解决方案。

在这种情况下，确保安全的唯一方法是确保您的应用程序能够理解100％的查询，这意味着在您的程序中对其进行解析，并仅允许您认为安全的操作。