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

Question

我正在使用 SQL Server 2008 中的存储过程，并且我了解到必须INSERT INTO 一个已预定义的临时表才能处理数据。没关系，除了如果我不是编写存储过程的人(除了列出其定义并阅读代码之外)，我该如何弄清楚如何定义我的临时表？

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

Answer 1

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

USE tempdb;
GO

CREATE PROCEDURE dbo.my_procedure
AS
BEGIN
    SET NOCOUNT ON;

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

有一种相当复杂的方法可以确定存储过程将输出的元数据。有几个注意事项，包括该过程只能输出单个结果集，并且如果无法精确确定数据类型，则会对数据类型进行最佳猜测。它需要使用OPENQUERY 和环回链接服务器，并将'DATA ACCESS' 属性设置为true。您可以检查 sys.servers 来查看是否已有有效的服务器，但让我们手动创建一个名为 loopback 的服务器:

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

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

现在您可以将其作为链接服务器进行查询，您可以将任何查询(包括存储过程调用)的结果用作常规SELECT。所以你可以这样做(注意数据库前缀很很重要，否则你会得到错误11529和2812):

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

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

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

一旦 #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');

(如果您使用的是 SQL Server 2000，则可以使用 syscolumns 执行类似的操作，但我没有方便的 2000 实例来验证等效查询。)

结果:

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

在麦金利峰，这会容易得多。同样，第一个结果集仍然存在限制，但您不必设置链接服务器并跳过所有这些麻烦。你可以直接说:

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

结果:

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

在 Denali 之前，我建议卷起袖子自己弄清楚数据类型会更容易。不仅因为完成上述步骤很乏味，还因为您比引擎更有可能做出正确(或至少更准确)的猜测，因为引擎做出的数据类型猜测将基于运行时输出，无需任何可能值域的外部知识。这个因素在 Denali 中也同样适用，因此不要以为新的元数据发现功能是万能的，它们只是让上述内容变得不那么乏味。

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

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