在运行时显示 SQL Server 查询结果中的数据类型和列大小 [英] Display DataType and Size of Column from SQL Server Query Results at Runtime

问题描述

有没有办法运行查询，然后让 SQL Server management studio 或 sqlcmd 或其他东西简单地显示接收到的每列的数据类型和大小.

Is there a way to run a query and then have SQL Server management studio or sqlcmd or something simply display the datatype and size of each column as it was received.

似乎必须存在此信息才能在服务器和客户端之间传输数据.如果能显示出来对我很有帮助.

Seems like this information must be present for the transmission of the data to occur between the server and the client. It would be very helpful to me if it could be displayed.

一点背景:我问的原因是因为我必须与无数遗留存储过程交互，每个存储过程有 50 到 5000 多行代码.我不想尝试遵循进出临时表、进入其他过程、进入字符串连接的 eval 语句等的神秘逻辑流程.我不希望保持对实现的了解，只是希望它们工作时会发生什么.不幸的是，遵循逻辑流程似乎是确定返回的确切内容的唯一方法，而无需尝试推断数据字符串表示的实际类型 om management studio studio 或例如从 .net 中的本机类型.

A little background: The reason I ask is because I must interface with countless legacy stored procedures with anywhere from 50 to 5000+ lines of code each. I do not want to have to try and follow the cryptic logic flow in and out of temp tables, into other procedures, into string concatenated eval statement and so on. I wish to maintain no knowledge of the implementation, simply what to expect when they work. Unfortunately following the logic flow seems to be the only way to figure out what exactly is being returned without trying to infer what the actual types of the data string representations om management studio studio or from the native type in .net for example.

澄清:我不是在问如何判断表格的类型或类似的静态内容.我很确定像 sp_help 这样的东西不会帮助我.我在问如何判断 sql server 类型(即 varchar(25)、int ...)是我得到的.此外，无法更改 sproc 的实现，因此请在您的解决方案中考虑这一点.我真的希望有一个我在某处错过的命令.非常感谢大家.

To clarify: I am not asking about how to tell the types of a table or something static like that. I'm pretty sure something like sp_help will not help me. I am asking how to tell what the sql server types (ie varchar(25), int...) are of what I have been given. Additionally, changing the implementation of the sprocs is not possible so please consider that in your solutions. I am really hoping there is a command I have missed somewhere. Much appreciation to all.

更新我想我真正要问的是当结果集源自使用临时表的查询时如何获取结果集的架构.我理解这是不可能的，但认为这个结论没有多大意义，因为毕竟数据正在传输.下面是一个会导致问题的存储过程示例.

Update I guess what I am really asking is how to get the schema of the result set when the result set originates from a query using a temp table. I understand this to be impossible but don't find much sense with that conclusion because the data is being transmitted after all. Here is an example of a stored procedure that would cause a problem.

CREATE PROCEDURE [dbo].[IReturnATempTable]
AS

Create table #TempTable 
( 
    MyMysteryColumn char(50)
)

INSERT #TempTable (
    MyMysteryColumn
) VALUES ( 
    'Do you know me?' ) 


select TOP 50 * FROM #TempTable 

推荐答案

有点远，尝试使用 SET FMTONLY ON(或关闭).根据 BOL 的说法，这仅向客户端返回元数据.可用于测试响应的格式，而无需实际运行查询."我怀疑这将包含您正在寻找的内容，因为 BCP 使用它.(我在调试一些非常奇怪的 BCP 问题时偶然发现了这个设置.)

A bit of a long shot, try messing around with SET FMTONLY ON (or off). According to BOL, this "Returns only metadata to the client. Can be used to test the format of the response without actually running the query." I suspect that this will inlcude what you're looking for, as BCP uses this. (I stumbled across this setting when debugging some very oddball BCP problems.)

这篇关于在运行时显示 SQL Server 查询结果中的数据类型和列大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！