View schema of resultset in SQL Server Management Studio

Question

Is there any way in Sql Server Management Studio (2008) whereby I can view the data types of each field in the result of a query?

In this case, I am running a stored procedure which returns a result set, and I would like to know the lengths of the nvarchar columns and precision of decimals.

In the past, I have created a view which contains the underlying query in the stored procedure, and then viewed the column list, but the query within the procedure is much too complex to do so in this case.

Any ideas?

Answer 1

Quick and dirty snippet, requires all the fields in the result set are named or aliased;

select * into #T 
from 
  openrowset('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec thedb.dbo.sp_whatever')
exec('use tempdb exec sp_columns #T drop table #T')

Answer 2

Your best bet may be to use OPENROWSET to store the output of the procedure into a table, then examine that table. Something like:

SELECT * INTO YourHoldingTable 
    FROM OPENROWSET('SQLNCLI', 'Server=YourServerName;Trusted_Connection=yes;', 'EXEC YourDatabase.YourSchema.YourProcedureName')
GO

sp_help 'YourHoldingTable'
GO

DROP TABLE 'YourHoldingTable'
GO