从 SQL Server 中的动态查询中获取选择列名 [英] Get Select Column names from dynamic query in SQL Server

问题描述

如果我有一个随机查询，并且想从这个查询中返回列名:

If I have a random query, and would like to return the column names from this query:

SELECT 1 ONE, 2 TWO, 'THREE' THREE

如何返回列名?甚至列数据类型也是如此.

How can I return the column names? Maybe even the column data type as well.

我可以在 c# 中做到这一点，但我正在寻找一种方法来完全在 Ms Sql 中做到这一点.

I can do this in c#, but I am looking for a way to do this all purely inside of Ms Sql.

这可能吗，如果可能，怎么办?

Is this possible, if so how?

我是否必须创建一个临时表，然后检查信息架构表?或者我可以用另一种方式来做吗?

Would I have to create a temporary table, then check the info schema table? Or can I do it another way?

推荐答案

您可以使用 sp_describe_first_result_set 描述从任意 SQL 语句返回的列.例如:

You can use sp_describe_first_result_set to describe the columns returned from an arbitrary SQL statement. For example:

EXEC sp_describe_first_result_set @tsql = 'SELECT 1 ONE, 2 TWO, ''THREE'' THREE'

你会得到这样的结果fiddle.

对于旧版本的 SQL Server，您还可以查看 SETFMTONLY ON.

For older versions of SQL Server, you can also look at SET FMTONLY ON.

这篇关于从 SQL Server 中的动态查询中获取选择列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！