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

2022-01-16 00:00:00 database sql-server relational-database sql-server-2008-r2

我们可以得到 SELECT Query 返回的结果的模式吗?下面的代码:

Can we get schema of the result returned by SELECT Query? Below code:

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'";

此代码返回列名以及指定表的数据类型.我的要求是从选择查询中获取列名和数据类型，而不是直接指定表名.喜欢

This code returns column names along with datatype for specified table. My requirement is to get column names along with datatype from a select query rather than directly specifying table name. like 

  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'";

虽然这行不通.

推荐答案

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

You can SELECT the query result INTO a temporary table, and from that table get the metadata. If you choose a solution like this you will need to make sure that the name of the temporary table is UNIQUE per call, otherwise meta-data in information_schema views will be shared across the sessions.

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# 中更轻松地执行此操作，这可能不太容易出错.获取查询结果集的元数据非常容易:

Or, if you want you can do it easier In C# which will probably less error prone. It is very easy to get the meta-data for a result set of a query:

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

相关文章