Create table syntax for stored procedure's return

Question

I'm wondering if there is a easy way to get a create table syntax for stored procedure's return.

For example we have a stored proc:

CREATE PROCEDURE [dbo].[usp_branches]
AS BEGIN
    select * from branches
END

and then I need something like this

insert into @tempBranches
exec usp_branches

Is there a way that I can easily get create table syntax from stored procedure's return? So for this example I will get this

DECLARE @tempBranches TABLE
    (
        BranchID int
        ,BranchName varchar(25)
    )

Answer 1

In SQL Server 2012, yes. There is new functionality that will retrieve metadata based on an ad hoc SQL string or an object name (see https://sqlblog.org/2010/12/20/sql-server-v-next-denali-metadata-enhancements for more details on that).

In earlier versions (you forgot to tell us which version), there are some less reliable workarounds. e.g.

SELECT * INTO #table FROM OPENROWSET('SQLNCLI', 
    'Server=(local);Trusted Connection=Yes;', 
    'EXEC yourdatabase.dbo.usp_branches;');

In order to do this you will first need to say:

EXEC sp_configure 'show adv', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'ad hoc dist', 1;
RECONFIGURE WITH OVERRIDE;
GO

Now you can build the CREATE TABLE statement based on tempdb.sys.columns where name LIKE '#table%';. Or safer where [object_id] = OBJECT_ID('tempdb..#table');.

Just keep in mind that like the new metadata functionality, if there is more than one resultset (or the shape can change depending on the input), all bets are off.