How to determine the datatypes of the results of a SQL?

Question

We have a SQL query that pulls a large number of fields from many tables/views from a database. We need to put a spec together for integration with a 3rd party, what is the quickest way to compile the data types of the result set?

Clarifications:

• There are 25+ tables/views involved, so functions at the table level will still be cumbersome.
• All work is currently being done in Microsoft SQL Server Management Studio.

Answer 1

In general for an existing table or view you'd want to use the system catalogs or INFORMATION_SCHEMA.COLUMNS, but if it's an arbitrary query you're looking at it's a bit more difficult.

I would use the sys.dm_exec_describe_first_result_set dynamic management view. Often, the biggest pain is having to single-quote escape your query.

DECLARE @tsql nvarchar(max) = N'SELECT YourFields FROM YourQuery';

SELECT *
FROM sys.dm_exec_describe_first_result_set(@tsql, null, 0);

See the documentation for more information.

Alternatively, you can use the sp_describe_first_result_set stored procedure and get basically the same results. (The dynamic management view is nice because you can more easily filter with a WHERE or sort with an ORDER BY.)

DECLARE @tsql nvarchar(max) = N'SELECT YourFields FROM YourQuery';

EXEC sp_describe_first_result_set @tsql, null, 0;

Again, see the doc for more info.

Answer 2

You might use few quick SQL statements to view result column type, by using temp table.

Temp tables is a little better then a view, as they are connection-local scope and will be cleared once disconnect.

All you need is inject few keyword as follow

SELECT
TOP 0 -- to speed up without access data
your,original,columns
INTO #T -- temp table magic
FROM originalTablesJoins
Order by anything
exec tempdb.sys.sp_columns #T
drop table #T

or;

SELECT TOP 0 *
INTO #T
FROM (
  select your,original,columns from originalTablesJoins -- remove order by if any
) x
exec tempdb.sys.sp_columns #T
drop table #T

Note: inspired by View schema of resultset in SQL Server Management Studio