SQL 检索存储过程的列名和类型？

Retrieve column names and types of a stored procedure?

提问by Wang Wei

Possible Duplicate:
Retrieve column definition for stored procedure result set

可能的重复：
检索存储过程结果集的列定义

I use the following SQL to get column names and types for a table or view:

我使用以下 SQL 来获取表或视图的列名和类型：

DECLARE @viewname varchar (250);

select a.name as colname,b.name as typename 
from syscolumns a, systypes b -- GAH!
where a.id = object_id(@viewname) 
and a.xtype=b.xtype 
and b.name <> 'sysname'

How do I do something similar for the output columns of a stored procedure?

如何对存储过程的输出列执行类似的操作？

回答by Aaron Bertrand

[I just realized I've answered this question before]

[我刚刚意识到我以前回答过这个问题]

Doing this for a stored procedure is a lot more complicated than it is for a view or table. One of the problems is that a stored procedure can have multiple different code paths depending on input parameters and even things you can't control like server state, time of day, etc. So for example what you would expect to see as the output for this stored procedure? What if there are multiple resultsets regardless of conditionals?

为存储过程执行此操作比为视图或表执行此操作要复杂得多。问题之一是，存储过程可以有多个不同的代码路径，具体取决于输入参数，甚至是您无法控制的事情，例如服务器状态、一天中的时间等。例如，您希望看到的输出这个存储过程？如果不管条件如何有多个结果集怎么办？

CREATE PROCEDURE dbo.foo
  @bar INT
AS
BEGIN
  SET NOCOUNT ON;

  IF @bar = 1
    SELECT a, b, c FROM dbo.blat;
  ELSE
    SELECT d, e, f, g, h FROM dbo.splunge;
END
GO

If your stored procedure does not have code paths and you are confident that you will always see the same result set (and can determine in advance what values should be supplied if a stored procedure has non-optional parameters), let's take a simple example:

如果您的存储过程没有代码路径并且您确信您将始终看到相同的结果集（并且可以提前确定如果存储过程具有非可选参数应提供哪些值），让我们举一个简单的例子：

CREATE PROCEDURE dbo.bar
AS
BEGIN
  SET NOCOUNT ON;

  SELECT a = 'a', b = 1, c = GETDATE();
END
GO

FMTONLY

FMTONLY

One way is to do something like this:

一种方法是做这样的事情：

SET FMTONLY ON;
GO
EXEC dbo.bar;

This will give you an empty resultset and your client application can take a look at the properties of that resultset to determine column names and data types.

这将为您提供一个空的结果集，您的客户端应用程序可以查看该结果集的属性以确定列名和数据类型。

Now, there are a lot of problems with SET FMTONLY ON;that I won't go into here, but at the very least it should be noted that this command is deprecated- for good reason. Also be careful to SET FMTONLY OFF;when you're done, or you'll wonder why you create a stored procedure successfully but then can't execute it. And no, I'm not warning you about that because it just happened to me. Honest. :-)

现在，有很多问题SET FMTONLY ON;我不会在这里讨论，但至少应该注意的是，这个命令已被弃用- 有充分的理由。SET FMTONLY OFF;当你完成的时候也要小心，否则你会想知道为什么你成功地创建了一个存储过程，但之后却无法执行它。不，我不会就此警告你，因为它只是发生在我身上。诚实。:-)

OPENQUERY

公开查询

By creating a loopback linked server, you can then use tools like OPENQUERYto execute a stored procedure but return a composable resultset (well, please accept that as an extremely loose definition) that you can inspect. First create a loopback server (this assumes a local instance named FOO):

通过创建环回链接服务器，然后您可以使用诸如OPENQUERY执行存储过程之类的工具，但返回您可以检查的可组合结果集（好吧，请接受这是一个非常松散的定义）。首先创建一个环回服务器（这里假设有一个名为 的本地实例FOO）：

USE master;
GO
EXEC sp_addlinkedserver @server = N'.\FOO', @srvproduct=N'SQL Server'
GO
EXEC sp_serveroption @server=N'.\FOO', @optname=N'data access', 
  @optvalue=N'true';

Now we can take the procedure above and feed it into a query like this:

现在我们可以采用上面的过程并将其输入到这样的查询中：

SELECT * INTO #t 
FROM OPENQUERY([.\FOO], 'EXEC dbname.dbo.bar;')
WHERE 1 = 0;

SELECT c.name, t.name
FROM tempdb.sys.columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
WHERE c.[object_id] = OBJECT_ID('tempdb..#t');

This ignores alias types (formerly known as user-defined data types) and also may show two rows for columns defined as, for example, sysname. But from the above it produces:

这会忽略别名类型（以前称为用户定义的数据类型），并且还可能为定义为例如 的列显示两行sysname。但从上面它产生：

name   name
----   --------
b      int
c      datetime
a      varchar

Obviously there is more work to do here - varchardoesn't show length, and you'll have to get precision / scale for other types such as datetime2, timeand decimal. But that's a start.

显然，这里还有更多工作要做 -varchar不显示长度，并且您必须获得其他类型的精度/比例，例如datetime2,time和decimal。但这是一个开始。

SQL Server 2012

SQL Server 2012

There are some new functions in SQL Server 2012 that make metadata discovery much easier. For the above procedure we can do the following:

SQL Server 2012 中有一些新功能使元数据发现变得更加容易。对于上述过程，我们可以执行以下操作：

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
(
  OBJECT_ID('dbo.bar'), 
  NULL
);

Among other things this actually provides precision and scale and resolves alias types for us. For the above procedure this yields:

除其他外，这实际上为我们提供了精度和比例并解析了别名类型。对于上述过程，这将产生：

name   system_type_name
----   ----------------
a      varchar(1)
b      int
c      datetime

Not much difference visually but when you start getting into all the different data types with various precision and scale you'll appreciate the extra work this function does for you.

视觉上没有太大区别，但是当您开始使用各种精度和比例的所有不同数据类型时，您会欣赏此功能为您所做的额外工作。

The downside: In SQL Server 2012 at least these functions only work for the firstresultset (as the name of the function implies).

缺点：在 SQL Server 2012 中，至少这些函数仅适用于第一个结果集（正如函数名称所暗示的那样）。

回答by sgeddes

Are you trying to return all the stored procedures and all of their parameters? Something like this should work for that.

您是否试图返回所有存储过程及其所有参数？像这样的事情应该为此工作。

select * from information_schema.parameters

If you need to get the columns returned from a stored procedure, take a look here:

如果您需要获取从存储过程返回的列，请查看此处：

Get column names/types returned from a stored procedure

获取从存储过程返回的列名/类型