检索存储过程结果集的列定义

I'm working with stored procedures in SQL Server 2008 and I've come to learn that I have to INSERT INTO a temp table that has been predefined in order to work with the data. That's fine, except how do I figure out how to define my temp table, if I'm not the one that wrote the stored procedure other than listing its definition and reading through the code?

我正在使用SQL Server 2008中的存储过程，我开始了解到我必须插入到一个预定义的temp表中，以便处理数据。这很好，但是如果我不是编写存储过程的人，除了列出定义并阅读代码之外，我如何确定如何定义我的临时表?

For example, what would my temporary table look like for `EXEC sp_stored_procedure'? That is a simple stored procedure, and I could probably guess at the data types, but it seems there must be a way to just read the type and length of the columns returned from executing the procedure.

例如，“EXEC sp_stored_procedure”的临时表是什么样子的?这是一个简单的存储过程，我可以猜测数据类型，但是似乎必须有一种方法来读取执行过程返回的列的类型和长度。

5 个解决方案

#1

So let's say you have a stored procedure in tempdb:

假设在tempdb中有一个存储过程

USE tempdb;
GO

CREATE PROCEDURE dbo.my_procedure
AS
BEGIN
    SET NOCOUNT ON;

    SELECT foo = 1, bar = 'tooth';
END
GO

There is a quite convoluted way you can go about determining the metadata that the stored procedure will output. There are several caveats, including the procedure can only output a single result set, and that a best guess will be made about the data type if it can't be determined precisely. It requires the use of OPENQUERY and a loopback linked server with the 'DATA ACCESS' property set to true. You can check sys.servers to see if you already have a valid server, but let's just create one manually called loopback:

有一种非常复杂的方法可以确定存储过程将输出的元数据。这里有几个注意事项，包括过程只能输出一个结果集，如果不能精确地确定数据类型，那么将对数据类型进行最佳猜测。它需要使用OPENQUERY和具有“数据访问”属性设置为true的环回链接服务器。你可以检查系统。服务器来查看是否已经有一个有效的服务器，但是让我们手动创建一个称为loopback的服务器:

EXEC master..sp_addlinkedserver 
    @server = 'loopback',  
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = @@SERVERNAME;

EXEC master..sp_serveroption 
    @server = 'loopback', 
    @optname = 'DATA ACCESS',
    @optvalue = 'TRUE';

Now that you can query this as a linked server, you can use the result of any query (including a stored procedure call) as a regular SELECT. So you can do this (note that the database prefix is important, otherwise you will get error 11529 and 2812):

既然可以将其作为链接服务器进行查询，那么就可以将任何查询(包括存储过程调用)的结果作为常规选择。因此，您可以这样做(注意，数据库前缀很重要，否则会出现错误11529和2812):

SELECT * FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');

If we can perform a SELECT *, we can also perform a SELECT * INTO:

如果我们可以执行SELECT *，我们也可以执行SELECT * INTO:

SELECT * INTO #tmp FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');

And once that #tmp table exists, we can determine the metadata by saying (assuming SQL Server 2005 or greater):

一旦#tmp表存在，我们可以通过以下方式确定元数据(假设SQL Server 2005或更高版本):

SELECT c.name, [type] = t.name, c.max_length, c.[precision], c.scale
  FROM sys.columns AS c
  INNER JOIN sys.types AS t
  ON c.system_type_id = t.system_type_id
  AND c.user_type_id = t.user_type_id
  WHERE c.[object_id] = OBJECT_ID('tempdb..#tmp');

(If you're using SQL Server 2000, you can do something similar with syscolumns, but I don't have a 2000 instance handy to validate an equivalent query.)

(如果您正在使用SQL Server 2000，您可以使用syscolumns执行类似的操作，但是我没有一个2000实例可以方便地验证相同的查询。)

Results:

结果:

name      type    max_length precision scale
--------- ------- ---------- --------- -----
foo       int              4        10     0
bar       varchar          5         0     0

In Denali, this will be much, much, much easier. Again there is still a limitation of the first result set but you don't have to set up a linked server and jump through all those hoops. You can just say:

在德纳里，这将会容易得多。第一个结果集仍然有一个限制，但是您不需要设置一个链接服务器并跳过所有这些限制。你可以说:

DECLARE @sql NVARCHAR(MAX) = N'EXEC tempdb.dbo.my_procedure;';

SELECT name, system_type_name
    FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);

Results:

结果:

name      system_type_name
--------- ----------------
foo       int             
bar       varchar(5)      

Until Denali, I suggest it would be easier to just roll up your sleeves and figure out the data types on your own. Not just because it's tedious to go through the above steps, but also because you are far more likely to make a correct (or at least more accurate) guess than the engine will, since the data type guesses the engine makes will be based on runtime output, without any external knowledge of the domain of possible values. This factor will remain true in Denali as well, so don't get the impression that the new metadata discovery features are a be-all end-all, they just make the above a bit less tedious.

在Denali之前，我建议更简单的做法是卷起袖子，自己计算数据类型。不仅因为它是乏味的经历上面的步骤,但也因为你更有可能做出一个正确的(或者至少更准确)比引擎会猜,因为数据类型的猜测引擎让将基于运行时输出,没有任何外部知识域的可能值。这个因素在Denali中也将是正确的，所以不要认为新的元数据发现特性是最重要的，它们只是让上面的内容变得不那么单调。

Oh and for some other potential gotchas with OPENQUERY, see Erland Sommarskog's article here:

噢，对于OPENQUERY的其他潜在问题，请参阅Erland Sommarskog的文章:

http://www.sommarskog.se/share_data.html#OPENQUERY

http://www.sommarskog.se/share_data.html OPENQUERY

#2

A less sophisticated way (that could be sufficient in some cases): edit your original SP, after the final SELECT and before the FROM clause add INSERT INTO tmpTable to save the SP result in tmpTable.

一种不太复杂的方法(在某些情况下这就足够了):编辑原始SP，在最终选择之后，在FROM子句add INSERT到tmpTable之前，将SP结果保存到tmpTable中。

Run the modified SP, preferably with meaningful parameters in order to get actual data. Restore the original code of the procedure.

运行修改后的SP，最好使用有意义的参数，以便获得实际数据。还原过程的原始代码。

Now you can get the script of tmpTable from SQL server management studio or query sys.columns to get fields descriptions.

现在您可以从SQL server management studio或query sys获得tmpTable的脚本。获取字段描述的列。

#3

Here is some code that I wrote. The idea is (as someone else stated) is to get the SP code, modify it and execute it. However, my code does not change the original SP.

这是我写的一些代码。这个想法(正如其他人所说的)是获取SP代码，修改它并执行它。但是，我的代码并没有改变原来的SP。

First step, get the definition of the SP, strip the 'Create' part out and get rid of the 'AS' after the declaration of parameters, if exists.

第一步，得到SP的定义，去掉“Create”部分，去掉参数声明后的“AS”(如果存在的话)。

Declare @SPName varchar(250)
Set nocount on

Declare @SQL Varchar(max), @SQLReverse Varchar(MAX), @StartPos int, @LastParameterName varchar(250) = '', @TableName varchar(36) = 'A' + REPLACE(CONVERT(varchar(36), NewID()), '-', '')

Select * INTO #Temp from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME = 'ADMIN_Sync_CompareDataForSync'

if @@ROWCOUNT > 0
    BEGIN
        Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', 'Declare') 
        from INFORMATION_SCHEMA.ROUTINES 
        where ROUTINE_NAME = @SPName

        Select @LastParameterName = PARAMETER_NAME + ' ' + DATA_TYPE + 
            CASE WHEN CHARACTER_MAXIMUM_LENGTH is not null THEN '(' + 
                CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH) END + ')' ELSE '' END 
        from #Temp 
        WHERE ORDINAL_POSITION = 
            (Select MAX(ORDINAL_POSITION) 
            From #Temp)

        Select @StartPos = CHARINDEX(@LastParameterName, REPLACE(@SQL, '  ', ' '), 1) + LEN(@LastParameterName)
    END
else
    Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', '') from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = @SPName

DROP TABLE #Temp

Select @StartPos = CHARINDEX('AS', UPPER(@SQL), @StartPos)

Select @SQL = STUFF(@SQL, @StartPos, 2, '')

(Note the creation of a new table name based on a unique identifier) Now find the last 'From' word in the code assuming this is the code that does the select that returns the result set.

(注意创建一个基于唯一标识符的新表名)现在找到代码中的最后一个“From”字，假设这是执行返回结果集的select的代码。

Select @SQLReverse = REVERSE(@SQL)

Select @StartPos = CHARINDEX('MORF', UPPER(@SQLReverse), 1)

Change the code to select the resultset into a table (the table based on the uniqueidentifier)

更改代码，将resultset选择为一个表(基于惟一标识符的表)

Select @StartPos = LEN(@SQL) - @StartPos - 2

Select @SQL = STUFF(@SQL, @StartPos, 5, ' INTO ' + @TableName + ' FROM ')

EXEC (@SQL)

The result set is now in a table, it does not matter if the table is empty!

结果集现在在一个表中，表是否为空并不重要!

Lets get the structure of the table

让我们看看这个表的结构

Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

You can now do your magic with this

你现在可以用这个魔法了

Don't forget to drop that unique table

不要忘记删除那个唯一的表

Select @SQL = 'drop table ' + @TableName

Exec (@SQL)

Hope this helps!

希望这可以帮助!

#4

It looks like in SQL 2012 there is a new SP to help with this.

看起来在SQL 2012中有一个新的SP来帮助解决这个问题。

exec sp_describe_first_result_set N'PROC_NAME'

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql

#5

If you are working in an environment with restricted rights where things like loopback linked server seems black magic and are definitely "no way!", but you have a few rights on schema and only a couple of stored procedure to process there is a very simple solution.

如果您正在一个权限受限的环境中工作，那么loopback链接服务器之类的东西看起来就像魔法，而且绝对是“不可能”!，但是您在模式上有一些权限，只有几个存储过程可以处理，有一个非常简单的解决方案。

You can use the very helpful SELECT INTO syntax, which will create a new table with result set of a query.

您可以使用非常有用的SELECT INTO语法，它将创建一个包含查询结果集的新表。

Let's say your procedure contains the following Select query :

假设您的过程包含以下Select查询:

SELECT x, y, z
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...

Instead replace it by :

取而代之的是:

SELECT x, y, z
INTO MyOutputTable
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...

When you will execute it, it will create a new table MyOutputTable with the results returned by the query.

当您执行它时，它将创建一个新的表MyOutputTable，其中包含查询返回的结果。

You just have to do a right click on its name to get the table definition.

只需右键单击它的名称就可以获得表定义。

That's all !

这是所有!

SELECT INTO only require the ability to create new tables and also works with temporary tables (SELECT... INTO #MyTempTable), but it could be harder to retrieve the definition.

SELECT INTO只需要创建新表的能力，也可以使用临时表(SELECT…)进入#MyTempTable)，但要检索定义可能更困难。

However of course if you need to retrieve the output definition of a thousands SP, it's not the fastest way :)

当然，如果您需要检索数千个SP的输出定义，这不是最快的方法:)