获取从存储过程返回的列名/类型 [英] Get column names/types returned from a stored procedure

问题描述

是否有一种通过元数据(也许是Information_Schema?)的方式来获取存储过程将返回的列的列表?我正在尝试使一些代码生成自动化，这将极大地帮助您……

Is there a way via metadata (Information_Schema, perhaps?) to get a list of the columns a sproc will return? I'm trying to automate some code generation and that would help tremendously...

推荐答案

除非您准备分析INFORMATION_SCHEMA.ROUTINES中ROUTINE_DEFINITION的内容，否则最好的选择是执行过程，并从中读取列信息.返回记录.

Unless you're prepared to parse the contents of ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, then your best bet will be to execute the procedures, and read the column information from the records returned.

在.NET中，您可以通过将存储过程的结果读取到DataTable中并查询

In .NET you can do this by reading the results of the stored procedure into a DataTable and querying the Columns property.

之所以没有做到这一点的简单方法，是因为存储过程可能会根据参数返回不同的结果集.没有像用户定义函数那样的固定结果集格式.

The reason there's no easy way to do this is a stored procedure could potentially return different result sets based on the parameters. There's no fixed result set format like there is with user defined functions.

修改

如其他答案中所述，您将需要使用设置FMTONLY ON 以确保不返回任何数据.在某些情况下，SET FMTONLY无法使用，例如在存储过程中使用#temp表时，但有一个

As mentioned in the other answer, you will need to use SET FMTONLY ON to ensure no data is returned. There are some situations where SET FMTONLY won't work, e.g. when using #temp tables in your stored procedures, but there is a workaround.

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