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)
    )

a duplicate of this: http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure — Denis Valeev, Mar 06 '12 at 21:03

Aaron Bertrand · Accepted Answer · 2018-11-11T00:40:18.810

2

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.

edited Nov 11 '18 at 00:40

answered Mar 06 '12 at 20:59

Aaron Bertrand

272,866
37
466
490

@DenisValeev thanks, frankly I think it's a poor solution (there should be better ways to retrieve result metadata), but it sure is a popular workaround. :-) – Aaron Bertrand Mar 06 '12 at 21:04
I would just go inside the stored procedure and edit the final select to output that into a staging table which could then be scripted to get the metadata. It's especially true for large tables in terms of columns with pretty hard names to spell. – Denis Valeev Mar 06 '12 at 21:08
it is for 2008 but I can try it on server because of security settings. 'SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.' – Cherven Mar 06 '12 at 21:11
@DenisValeev well that's not really a "better" solution - meaning one I think the SQL Server team could deliver, not one we would cobble together ourselves. – Aaron Bertrand Mar 06 '12 at 21:11
@Denis, what is staging table? – Cherven Mar 06 '12 at 21:12
@Cherven a staging table is a table that helps you get things done; a helper table, a temporary table where you're manipulating with data – Denis Valeev Mar 06 '12 at 21:15
@Cherven in sql server you can output resulting data set into another table by using `into` keyword like so: `select * into branches2 from branches` – Denis Valeev Mar 06 '12 at 21:17

Create table syntax for stored procedure's return

1 Answers1