查看可执行文件的运行架构_在运行查询之前如何查看结果集的架构

查看可执行文件的运行架构

介绍 (Introduction)

Suppose you’ve been asked to run a query against some SQL Server database but you don’t know anything more than the name of a stored procedure that you need to call. If you are going to do anything with the result set, you need to know at least the names and types of the columns to expect. This is actually a problem faced by many applications, including SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS).

假设要求您对某些SQL Server数据库运行查询，但是除了您需要调用的存储过程的名称之外，您什么都不知道。 如果要对结果集进行任何操作，则至少需要知道期望的列的名称和类型。 这实际上是许多应用程序所面临的问题，包括SQL Server集成服务（SSIS）和SQL Server报告服务（SSRS）。

In this article, we’ll look at how SSIS and SSRS handle this problem and how you can exploit the solution to your advantage.

在本文中，我们将研究SSIS和SSRS如何处理此问题以及如何利用该解决方案来发挥自己的优势。

久经考验的方法 (The Tried and True Method)

If you wanted to discover the schema of the (first) result set returned by a stored procedure, you needed some way of executing the stored procedure without – well – executing it! After all, you want to know what to expect, so that you can set up any data structures you need to receive the results. You need to know that the first column is called “AnInteger” and that it is of datatype integer, the second column is called “ADate” and is a date type, and so on. At the same time, you don’t want to just run the stored procedure, about which you know little. Perhaps it queries local and remote servers, does a massive join and takes an hour to run before the first row is returned! Clearly that is not acceptable.

如果要发现存储过程返回的（第一个）结果集的模式，则需要某种方式来执行存储过程，而无需–好–执行它！ 毕竟，您想知道会发生什么，以便可以设置接收结果所需的任何数据结构。 您需要知道第一列称为“ AnInteger”，并且其数据类型为整数，第二列称为“ ADate”，并且为日期类型，依此类推。 同时，您不想只运行很少了解的存储过程。 也许它会查询本地和远程服务器，进行大规模连接，并需要一个小时才能运行返回第一行！ 显然，这是不可接受的。

Before SQL Server 2012, you could achieve this by using the following SET command:

在SQL Server 2012之前，您可以使用以下SET命令来实现：

 
SET FMTONLY ON;
 

The name implies its function. It ONLY returns the ForMaT of the results, not the results themselves. With this setting, when a stored procedure is executed, SQL Server will follow all possible logic paths until it has some result set. Then, it returns the format of that set. No actual results are returned.

该名称暗含其功能。 它仅返回结果的F或M a T ，而不返回结果本身。 使用此设置，在执行存储过程时，SQL Server将遵循所有可能的逻辑路径，直到设置了某些结果。 然后，它返回该集合的格式。 没有实际结果返回。

When I said “all possible logic paths” I meant it literally. No actual conditions are checked. That means that if my stored procedure reads, in part:

当我说“所有可能的逻辑路径”时，我的意思是字面意思。 没有检查实际情况。 这意味着如果我的存储过程部分读取：

 
IF 1 = 0
    SELECT 1 AS One, 'Santa' as SaintNick;
ELSE 
    SELECT '42' as TheAnswer, 42 as [6 x 7];
 

SQL Server will return the format of the first select! It ignores the condition in its quest for the first result set. In this case, it will inform the caller that the result set consists of two columns called “One” and “SaintNick” respectively, with datatypes integer and character.

SQL Server将返回第一个选择的格式！ 它忽略对第一个结果集的搜索条件。 在这种情况下，它将通知调用方该结果集由分别称为“ One”和“ SaintNick”的两列组成，其数据类型为integer和character。

This type of call is used in a .NET program, which can then read the results into a data table for further processing. See “How To Retrieve Column Schema by Using the DataReader GetSchemaTable Method” in the Useful Links section for more details.

在.NET程序中使用这种类型的调用，然后该程序可以将结果读入数据表以进行进一步处理。 有关更多详细信息，请参见“有用的链接”部分中的“如何使用DataReader GetSchemaTable方法检索列模式”。

Here’s a snippet of what that might look like in C#:

以下是C＃中的代码片段：

 
SqlConnection cn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
DataTable schemaTable;
SqlDataReader myReader;
 
//Open a connection to the SQL Server MyDb database.
cn.ConnectionString = 
"Data Source=server;User ID=login;Password = password; Initial Catalog = 
My DB"; cn.Open();
 
//Retrieve records from the MyTable table into a DataReader.
cmd.Connection = cn;
cmd.CommandText = "SELECT * FROM MyTable";
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
 
//Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable();
 

After the boilerplate code, you can see that the ExecuteReader method is called with a special flag asking for the KeyInfo. This causes the query to be executed with FMTONLY ON. The results are not a normal result set but are passed as a .NET object which can then be processed with a DataReader using the GetSchemaTable method. The name, data type, size and other attributes are available for each column returned by the query, among other information. Refer to the official documentation for more details.

在样板代码之后，您可以看到ExecuteReader方法被调用，并带有一个特殊的标志，要求输入KeyInfo。 这将使查询在FMTONLY ON下执行。 结果不是正常的结果集，而是作为.NET对象传递的，然后可以使用GetSchemaTable方法与DataReader进行处理。 名称，数据类型，大小和其他属性可用于查询返回的每一列以及其他信息。 有关更多详细信息，请参考官方文档。

SQL Server 2012及更高版本 (SQL Server 2012 and Forward )

Starting with SQL Server 2012, a new extended stored procedure is available to accomplish the same thing as SET FMTONLY ON.

从SQL Server 2012开始，可以使用新的扩展存储过程来完成与SET FMTONLY ON相同的操作。

sp_describe_first_result_set takes a T-SQL batch, containing one or more T-SQL statements (nvarchar(n) or nvarchar(max)) as input and returns a result set with 1 row per column detailing the names and datatypes of each column for the first result set found. As with the previous method, conditions are not checked and all paths are tried until a result set is found.

sp_describe_first_result_set接受一个T-SQL批处理，其中包含一个或多个T-SQL语句（nvarchar（n）或nvarchar（max））作为输入，并返回一个结果列，该列每列包含1行，详细说明了第一列的名称和数据类型。找到结果集。 与以前的方法一样，不检查条件，并尝试所有路径，直到找到结果集。

There is, however, one major difference with the previous method. The column information is returned as a normal result set. This means that you can easily see it in SQL Server Management Studio. For example:

但是，与以前的方法有一个主要区别。 列信息作为常规结果集返回。 这意味着您可以在SQL Server Management Studio中轻松看到它。 例如：

 
sp_describe_first_result_set N'SELECT 1 AS One, ''Santa'' as SaintNick;';
 

Yields, in part:

产量，部分：

There are several more columns available showing attributes such as collation, key information, identity status, case sensitivity and other things.

还有更多可用的列，这些列显示诸如排序规则，键信息，身份状态，区分大小写等属性。

对我有什么好处？ (What’s In It For Me?)

At this point, you may be saying, “Hey, this is interesting, but how can I use it? I’m not writing my own ETL engine!”, or something like that. Good question!

此时，您可能会说：“嘿，这很有趣，但是我如何使用它呢？ 我不是在编写自己的ETL引擎！”或类似的东西。 好问题！

Back at the start of this article, I commented, “queries local and remote servers.” Suppose that you are not just executing, but writing a view or stored procedure that does query remote servers. Further, suppose that at least one of those remote servers is not SQL Server. Perhaps it is Oracle, MySQL, DB2 or some other RDBMS. Now imagine that the processing to be done takes minutes or hours. As mentioned before, you don’t want to wait that long just to see the format of the result set. But, what can you do? You have to send the query to the remote machine! You have to await the results!!

回到本文开头，我评论说“查询本地和远程服务器”。 假设你不只是执行，但是写， 做查询远程服务器的视图或存储过程。 此外，假设这些远程服务器中的至少一个不是 SQL Server。 也许是Oracle，MySQL，DB2或其他一些RDBMS。 现在想象一下要完成的处理需要几分钟或几小时。 如前所述，您不想等待那么久，就只能看到结果集的格式。 但你能做什么呢？ 您必须将查询发送到远程计算机！ 您必须等待结果！！

Here’s where the FMTONLY trick comes in. (I still call it FMTONLY, even since SQL Server 2012, since it works the same way by testing all code paths.) Let’s start with an arbitrary query to a MySQL database:

这就是FMTONLY技巧的用处 。（即使从SQL Server 2012开始，我仍将其称为FMTONLY ，因为它通过测试所有代码路径以相同的方式工作。）让我们从对MySQL数据库的任意查询开始：

 
sp_describe_first_result_set N'SELECT 1 AS One, ''Santa'' as SaintNick;';
 

If the table has billions of rows and there is no index in column Three, the query may take a while. Plus we have no control over when MySQL starts sending rows back to SQL Server. However we know that we could add a LIMIT clause like this:

如果表中有数十亿行，并且第三列中没有索引，则查询可能需要一段时间。 另外，我们无法控制MySQL何时开始将行发送回SQL Server。 但是我们知道我们可以像这样添加一个LIMIT子句：

 
SELECT One, Two, Three FROM MyTable LIMIT 0;
 

This will likely come back instantly. But what’s the point? You need that WHERE clause! Well, suppose your query was inside an Integration Services package or a Reporting Services data set. When you first create the Source component (SSIS) or Data Set (SSRS), those systems will first query the format of the result set. Knowing that that means that the query will be running in FMTONLY mode, you can do something like this:

这很可能会立即回来。 但是有什么意义呢？ 您需要该WHERE子句！ 好吧，假设您的查询位于Integration Services包或Reporting Services数据集中。 首次创建源组件（SSIS）或数据集（SSRS）时，这些系统将首先查询结果集的格式。 知道这意味着查询将以FMTONLY模式运行，您可以执行以下操作：

 
DECLARE @SQL NVARCHAR(MAX) = '
SELECT One, Two, Three FROM MyTable WHERE Three = ''Three'';';
 
IF 1 = 0
    SET @SQL = REPLACE(@sql, 'WHERE Three = ''Three''', 'LIMIT 0');
 
SELECT One, Two Three FROM OPENQUERY(MySQLServer, @SQL);
 

Here, we test if the query is running in FMTONLY mode. Since we know that in FMTONLY mode, conditions are ignored, the SET statement will be executed, and the query will be altered to remove the WHERE clause and replace it with a LIMIT clause. Then the finished query is sent to MySQL (using OPENQUERY in this case). This should execute quickly, especially since we’re asking that no rows be returned!

在这里，我们测试查询是否以FMTONLY模式运行。 因为我们知道在FMTONLY模式下将忽略条件，所以将执行SET语句，并将更改查询以删除WHERE子句并将其替换为LIMIT子句。 然后，完成的查询将发送到MySQL（在这种情况下使用OPENQUERY ）。 这应该很快执行，尤其是因为我们要求不返回任何 行 ！

The consequence of this sleight of hand is that when you or SSIS or SSRS or one of many other similar programs is asking about the shape of the result set, the query executes quickly. However, when it is run “for real”, the WHERE clause is in effect! This speeds up your development without changing the final result.

这种轻率的结果是，当您或SSIS或SSRS或许多其他类似程序之一询问结果集的形状时，查询将快速执行。 但是，当它“真正”运行时，WHERE子句有效！ 这样可以加快开发速度，而不会改变最终结果。

我正在使用的程序是否以此方式工作？ (Does the Program I’m Using Work This Way?)

We know that SSIS works this way but suppose you have a third-party package and do not know (or the vendor won’t tell you) if it uses SET FMTONLY ON or sp_describe_first_result_set? SQL Server Profiler to the rescue!

我们知道SSIS可以这样工作，但是假设您有第三方软件包，并且不知道（或供应商不会告诉您）它是否使用SET FMTONLY ON或sp_describe_first_result_set ？ SQL Server Profiler可以解救！

If I fire up SQL Server Profiler and start a default trace against the target database, I can see all the SQL statements being executed. Then, I look for something like this:

如果启动SQL Server Profiler并针对目标数据库启动默认跟踪，则可以看到所有正在执行SQL语句。 然后，我寻找这样的东西：

Aha! The evidence is plain to see! The program is using FMTONLY mode and I can use the trick to get fast results when in that mode.

啊哈！ 证据显而易见！ 该程序正在使用FMTONLY模式，在该模式下，我可以使用技巧来快速获得结果。

解决临时表的问题 (Solving the Problem of Temp Tables)

If you use integration services, you’ probably hit this situation. To get data, you execute some stored procedure. However, the procedure is complicated and builds temp tables on its way to producing the finished result set. Here’s a toy example:

如果使用集成服务，则可能会遇到这种情况。 要获取数据，请执行一些存储过程。 但是，该过程很复杂，并且会在生成最终结果集的过程中建立临时表。 这是一个玩具示例：

 
CREATE PROCEDURE [dbo].[SQLShack2] 
AS
BEGIN
	CREATE TABLE #SQLShack (One int, SaintNick varchar(30), Christmas date);
	INSERT INTO #SQLShack (One, SaintNick, Christmas) VALUES
		(1, 'Santa', '20161225');
	SELECT One, SaintNick, Christmas FROM #SQLShack;
END
 

Now, let’s call this stored procedure in an Integration Services package:

现在，让我们在Integration Services包中调用此存储过程：

If I click on the Preview button, I receive a scary-looking error message:

如果单击“预览”按钮，则会收到看起来很恐怖的错误消息：

Visual studio cannot preview the results. It’s not that it cannot execute the stored procedure. The problem is that it cannot determine the format of the result set. Since the temp table is created at runtime, the format of the table is unknown when calling the sp_describe_first_result_set stored procedure or using SET FMTONLY ON, in SQL Server 2008. Now, you may think it should be able to figure it out! How hard can it be parsing a CREATE TABLE statement? After all, isn’t DDL one of the things SQL Server is supposed to be good at? Well, imagine a long, convoluted stored procedure that creates several temporary tables, sometimes with run-time-dependent schemas, then joins them together at the end to return a result set. Sound far-fetched? Think again! This kind of thing is actually quite common.

Visual Studio无法预览结果。 不是它不能执行存储过程。 问题在于它无法确定结果集的格式。 由于临时表是在运行时创建的，因此在SQL Server 2008中调用sp_describe_first_result_set存储过程或使用SET FMTONLY ON时，表的格式是未知的。现在，您可能认为它应该可以弄清楚！ 解析CREATE TABLE语句有多难？ 毕竟，DDL难道不是SQL Server应该擅长的事情之一吗？ 好吧，想象一下一个漫长而复杂的存储过程，该过程创建了几个临时表，有时具有与运行时相关的模式，然后在最后将它们连接在一起以返回结果集。 声音牵强？ 再想一想！ 这种事情实际上很普遍。

There are, however, limits to what SQL Server can do. Deriving a result set when temporary tables are used is one of its limitations. So, what do we do to make this package work? There are two methods, depending on which version of SQL Server we are running.

但是，SQL Server的功能受到限制。 使用临时表时派生结果集是其限制之一。 那么，我们该怎么做才能使此程序包起作用？ 有两种方法，具体取决于我们运行SQL Server版本。

If running SQL Server 2008 or earlier, we can change the stored procedure to use the FMTONLY trick! The revised procedure could look like this:

如果运行SQL Server 2008或更早版本，我们可以更改存储过程以使用FMTONLY技巧！ 修改后的程序如下所示：

 
BEGIN
	IF 1 = 0 BEGIN
		SELECT CONVERT(int, NULL) AS One
		     , CONVERT(varchar(30), NULL) AS SaintNick
		     , CONVERT(date, NULL) AS Christmas
	END
 
	CREATE TABLE #SQLShack (One int, SaintNick varchar(30), Christmas date);
	INSERT INTO #SQLShack (One, SaintNick, Christmas) VALUES
		(1, 'Santa', '20161225');
 
	SELECT One, SaintNick, Christmas FROM #SQLShack;
END
 

You can see the changes at the beginning of the BEGIN/END section. I test for FMTONLY and if that is the case, return a dummy result set with a format that matches the real result set. Changing the stored procedure this way will enable SSIS to successfully retrieve the result set format.

您可以在BEGIN / END部分的开头看到更改。 我测试了FMTONLY ，如果是这种情况，则返回一个虚拟结果集，其格式与实际结果集匹配。 以这种方式更改存储过程将使SSIS成功检索结果集格式。

Since SQL SERVER 2012, a better way is used: WITH RESULT SET. In the OLEDB Source Connector, we write the query like this:

从SQL SERVER 2012开始，使用了更好的方法： WITH RESULT SET 。 在OLEDB源连接器中，我们这样编写查询：

 
EXEC SQLShack2
WITH RESULT SETS 
((  One int
  , SaintNick varchar(30)
  , Christmas date
 ));
 

This will enable SSIS to successfully retrieve the format of the result set for systems running SQL Server 2012 and above.

这将使SSIS能够成功检索运行SQL Server 2012及更高版本的系统的结果集格式。

摘要 (Summary)

SET FMTONLY ON was devised as a way to instruct SQL Server to return only the format of the first result set. In SQL Server 2012 and up, this has been superseded with the new sp_describe_first_result_set extended stored procedure.

SET FMTONLY ON旨在指示SQL Server仅返回第一个结果集的格式。 在SQL Server 2012及更高版本中，此功能已被新的sp_describe_first_result_set扩展存储过程所取代。

We’ve seen that this can be useful when you want to short-circuit a long-running query that may use external databases. This is especially useful when running queries from SSIS, SSRS, and many other applications.

我们已经看到，当您要缩短可能使用外部数据库的长时间运行的查询时，这很有用。 当从SSIS，SSRS和许多其他应用程序运行查询时，这特别有用。

Knowing these techniques will save you time and energy whenever you use software that queries the result format or just want to know what a query will give you without actually executing it.

只要您使用查询结果格式的软件，或者只想知道查询会在不实际执行的情况下为您提供什么，了解这些技术将为您节省时间和精力。

有用的链接 (Useful Links)

• How To Retrieve Column Schema by Using the DataReader GetSchemaTable Method 如何使用DataReader GetSchemaTable方法检索列模式
• sp_describe_first_result_set sp_describe_first_result_set
• EXECUTE (WITH RESULT SETS) 执行（有结果集 ）

翻译自: https://www.sqlshack.com/see-schema-result-set-running-query/

查看可执行文件的运行架构