检索存储过程的列名和类型? [英] Retrieve column names and types of a stored procedure?

问题描述

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

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

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

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?

推荐答案

[我刚刚意识到我已经之前回答过这个问题]

[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.

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

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. :-)

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

By creating a loopback linked server, you can then use tools like OPENQUERY to 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):

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

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

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:

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

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

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

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:

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.

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

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

这篇关于检索存储过程的列名和类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！