sql server - 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

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