从 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屋!