选择查询返回的结果架构获取

Question

我们能否获得SELECT查询返回结果的模式？以下是代码：

string SQLQuery = "SELECT DISTINCT c.name 'Column Name',  t.Name 'Data type' FROM" +
                   " sys.columns c INNER JOIN " +
                   " sys.types t ON c.system_type_id = t.system_type_id" +
                   " LEFT OUTER JOIN " +
                   " sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id" +
                   " LEFT OUTER JOIN" +
                   " sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id" +
                   " WHERE" +
                   " c.object_id = OBJECT_ID('[DB].[dbo].[" + ddlTable.SelectedItem.Text + "]') AND t.name <> 'sysname'";

这段代码返回指定表的列名及其数据类型。我的需求是从select查询中获取列名及其数据类型，而不是直接指定表名，就像：

  string SQLQuery = "SELECT DISTINCT c.name 'Column Name',  t.Name 'Data type' FROM" +
                       " sys.columns c INNER JOIN " +
                       " sys.types t ON c.system_type_id = t.system_type_id" +
                       " LEFT OUTER JOIN " +
                       " sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id" +
                       " LEFT OUTER JOIN" +
                       " sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id" +
                       " WHERE" +
                       " c.object_id = OBJECT_ID('SELECT col1, col2 from table(s)') AND t.name <> 'sysname'";

虽然这样做无效。

- vikbehal

---

这个查询会被任何应用程序使用吗？比如 C# 应用程序或类似的应用程序？ - Peter Henell

@PeterHenell 是的，会的。 - vikbehal

根据您需要此信息的原因，答案可能会有所不同。为什么您需要获取查询结果的元数据？ - Peter Henell

@PeterHenell 我正在使用SharePoint。要求是将选择查询的结果填充到SharePoint列表中。列表的架构和数据将动态创建。 - vikbehal

Answer 1

你可以将查询结果SELECT INTO一个临时表中，并从该表中获取元数据。如果选择这样的解决方案，您需要确保每次调用临时表的名称是唯一的，否则information_schema视图中的元数据将在会话之间共享。

if OBJECT_ID('tempdb..#tmp') is not null drop table #tmp

select * 
into #tmp
from Customer where 1 = 0 
-- select with a false predicate in order to ONLY get
--     metadata of the query and no rows of data.

select * from #tmp
-- Then select metadata from information_schema view
select COLUMN_NAME, DATA_TYPE from tempdb.information_schema.columns where TABLE_NAME like '#tmp%'

Result:
CustomerId  int
CustomerType    int
Name    nvarchar
IsActive    bit

或者，如果你想更简单地完成这个任务，你可以使用C#语言，这样可能会减少错误的发生。获取查询结果集的元数据非常容易：

static void Main(string[] args)
        {
            string connStr = "Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True";
            SqlCommand cmd = new SqlCommand("select * from Orders where 1 = 0", new SqlConnection(connStr));

            SqlDataAdapter ad = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();
            ad.FillSchema(ds, SchemaType.Mapped);
            var metaTable = ds.Tables[0];

            foreach (DataColumn col in metaTable.Columns)
            {
                Console.WriteLine("{0} : {1}", col.DataType, col.ColumnName);
            }

         }

输出：

System.Int32 : OrderId
System.Int32 : CustomerId
System.Int32 : ArticleId
System.Decimal : TotalAmount
System.DateTime : OrderDate

- Peter Henell

---

我会尽快回复。谢谢。 - vikbehal

我建议在 information_schema 的选择列列表中添加 PRECISION 和 SCALE。 - TomNash

Answer 2

我知道这个问题针对的是SQL 2008，但值得一提的是，在2012及以上版本中有一个内置的存储过程叫做sp_describe_first_result_set，它可以得到所需的结果。

请参阅文档

- Jeff

---

感谢分享，Jeff。 - vikbehal

Answer 3

从你的例子看来，你正在使用C#或类似的语言。假设你想要获取模式数据以便在程序中进行操作而不是仅构建一个仅限于SQL的机制，我可能有一个解决方案。

给定任意的SELECT查询，我们可以使用SqlDataReader.GetSchemaTable获取模式。

不幸的是，我们需要执行查询，但我们可以通过将输出减少到0行来减少影响。假设我们总是有一个没有TOP <n>子句的SELECT语句，那么你可以使用：

var sql = "SELECT TOP 0 " + query.SubString("SELECT ".Length);

或者您可以像这样将整个内容包装在外部 SELECT 中：

var sql = "SELECT TOP 0 * FROM (" + query + ") qq";

这个代码可以捕获大多数表单，并且它的作用应该很明显。

接下来我们创建一个 DbCommand 查询和打开一个 DbDataReader 实例来获取架构。这个架构将放入一个 DataTable 中，您可以读取并输出。

DataTable schema;

using (var command = connection.CreateCommand())
{
    command.CommandText = sql;
    using (var reader = command.ExecuteReader())
        schema = reader.GetSchemaTable();
}

此时schema包含查询中每个字段的一行，其中包含您可能需要的所有信息。

我为自己的一个程序添加了这个扩展方法：

public static Dictionary<string, (Type DataType, bool AllowDBNull, bool IsReadOnly)> GetQuerySchema(this IDbConnection connection, string query)
{
    var conn = connection as DbConnection;
    if (conn == null)
        return null;

    if (conn.State != ConnectionState.Open)
        conn.Open();

    string sql;
    if (query.StartsWith("SELECT"))
        sql = "SELECT TOP 0 * FROM (" + query + ") qq";
    else
        sql = "SELECT TOP 0 * FROM " + query;

    DataTable schema;
    using (var command = conn.CreateCommand())
    {
        command.CommandText = sql;
        using (var reader = command.ExecuteReader())
            schema = reader.GetSchemaTable();
    }

    var res = 
        schema.Rows.OfType<DataRow>()
        .ToDictionary
        (
            _ => _.Field<string>("ColumnName"),
            _ => 
            (
                _.Field<Type>("DataType"),
                _.Field<bool>("AllowDBNull"),
                _.Field<bool>("IsReadOnly")
            )
        );
    return res;
}

- Corey