SQL Server Table definition from stored procedure result set
Is anyone aware of a way to create a table definition from a stored procedure result set?
I have a stored procedure which produces a result set with 30+ columns, I'd like to get this into a table without having to manually create all the table columns.
Is there a built in procedure that will dump out the column names and types..?
Thanks.
sql
sql-server tsql asked Mar 19 '10 at 14:13
SuperBrookSuperBrook
8227
add a comment |
Is anyone aware of a way to create a table definition from a stored procedure result set?
I have a stored procedure which produces a result set with 30+ columns, I'd like to get this into a table without having to manually create all the table columns.
Is there a built in procedure that will dump out the column names and types..?
Thanks.
sql
sql-server tsql asked Mar 19 '10 at 14:13
SuperBrookSuperBrook
8227
Not that I am aware of. Why would you want to?
– CResults
Mar 19 '10 at 14:19
To avoid having to manually create the table, I'm just trying to save some time really ... I've had issues in the past using 'INSERT EXEC' where the columns returned in the stored procedure don't exactly match that of the table. It can be a real pain when these procedures return so many columns.
– SuperBrook
Mar 19 '10 at 14:22
add a comment |
Is anyone aware of a way to create a table definition from a stored procedure result set?
I have a stored procedure which produces a result set with 30+ columns, I'd like to get this into a table without having to manually create all the table columns.
Is there a built in procedure that will dump out the column names and types..?
Thanks.
sql
sql-server tsql asked Mar 19 '10 at 14:13
SuperBrookSuperBrook
8227
Is anyone aware of a way to create a table definition from a stored procedure result set?
I have a stored procedure which produces a result set with 30+ columns, I'd like to get this into a table without having to manually create all the table columns.
Is there a built in procedure that will dump out the column names and types..?
Thanks.
sql
sql-server tsql sql
sql-server tsql asked Mar 19 '10 at 14:13
SuperBrookSuperBrook
8227
asked Mar 19 '10 at 14:13
SuperBrookSuperBrook
8227
asked Mar 19 '10 at 14:13
SuperBrookSuperBrook
8227
asked Mar 19 '10 at 14:13
SuperBrookSuperBrook
8227
asked Mar 19 '10 at 14:13
SuperBrookSuperBrook
8227
8227
Not that I am aware of. Why would you want to?
– CResults
Mar 19 '10 at 14:19
To avoid having to manually create the table, I'm just trying to save some time really ... I've had issues in the past using 'INSERT EXEC' where the columns returned in the stored procedure don't exactly match that of the table. It can be a real pain when these procedures return so many columns.
– SuperBrook
Mar 19 '10 at 14:22
add a comment |
Not that I am aware of. Why would you want to?
– CResults
Mar 19 '10 at 14:19
To avoid having to manually create the table, I'm just trying to save some time really ... I've had issues in the past using 'INSERT EXEC' where the columns returned in the stored procedure don't exactly match that of the table. It can be a real pain when these procedures return so many columns.
– SuperBrook
Mar 19 '10 at 14:22
Not that I am aware of. Why would you want to?
– CResults
Mar 19 '10 at 14:19
Not that I am aware of. Why would you want to?
– CResults
Mar 19 '10 at 14:19
To avoid having to manually create the table, I'm just trying to save some time really ... I've had issues in the past using 'INSERT EXEC' where the columns returned in the stored procedure don't exactly match that of the table. It can be a real pain when these procedures return so many columns.
– SuperBrook
Mar 19 '10 at 14:22
To avoid having to manually create the table, I'm just trying to save some time really ... I've had issues in the past using 'INSERT EXEC' where the columns returned in the stored procedure don't exactly match that of the table. It can be a real pain when these procedures return so many columns.
– SuperBrook
Mar 19 '10 at 14:22
add a comment |
4 Answers
4
active
oldest
votes
You "can" do it but I don't know if I'd recommend it:
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT
*
INTO
#table
FROM
OPENROWSET(
'SQLNCLI',
'SERVER=.;Trusted_Connection=yes',
'EXEC StoredProcedureName'
)
answered Oct 3 '11 at 18:38
AskenAsken
4,47173760
add a comment |
If this is a one time thing, you can try this (not in production!).
I usually just go to the child procedure and on the query that returns the result set, add INTO YourNewTable between the column select list and the FROM. Run the child procedure 1 time and then remove the change.
You can then go and look at the result set columns by looking at YourNewTable in SQl Server Management Studio, or any table meta data query like:
SELECT
*
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME='YourNewTable'
ORDER BY ORDINAL_POSITION
answered Mar 19 '10 at 14:44
KM.KM.
82.9k27149189
add a comment |
in your stored procedure, you will need to perform a "SELECT INTO" with a "WHERE 1 = 0" so it just creates an empty table.
If you want to define the data types:
convert or cast your column into that data type
if you want a NOT NULL constraint, then you must use in the select:
isnull(yourcolumn,'123') as yourcolumn and then in the where clause put WHERE yourcolumn <> '123'
answered Mar 27 '12 at 14:59
elviselvis
207210
add a comment |
Have you tried this?
DECLARE @sql NVARCHAR(MAX) = N'EXEC mydb.dbo.my_procedure;';
SELECT name, system_type_name, max_length, is_nullable
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);
It won't build the table for you, but you can quickly create it knowing the names and types of the column names.
answered Nov 23 '18 at 23:07
Shafiq JethaShafiq Jetha
429716
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f2477921%2fsql-server-table-definition-from-stored-procedure-result-set%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
You "can" do it but I don't know if I'd recommend it:
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT
*
INTO
#table
FROM
OPENROWSET(
'SQLNCLI',
'SERVER=.;Trusted_Connection=yes',
'EXEC StoredProcedureName'
)
answered Oct 3 '11 at 18:38
AskenAsken
4,47173760
add a comment |
You "can" do it but I don't know if I'd recommend it:
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT
*
INTO
#table
FROM
OPENROWSET(
'SQLNCLI',
'SERVER=.;Trusted_Connection=yes',
'EXEC StoredProcedureName'
)
answered Oct 3 '11 at 18:38
AskenAsken
4,47173760
add a comment |
You "can" do it but I don't know if I'd recommend it:
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT
*
INTO
#table
FROM
OPENROWSET(
'SQLNCLI',
'SERVER=.;Trusted_Connection=yes',
'EXEC StoredProcedureName'
)
answered Oct 3 '11 at 18:38
AskenAsken
4,47173760
You "can" do it but I don't know if I'd recommend it:
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT
*
INTO
#table
FROM
OPENROWSET(
'SQLNCLI',
'SERVER=.;Trusted_Connection=yes',
'EXEC StoredProcedureName'
)
answered Oct 3 '11 at 18:38
AskenAsken
4,47173760
answered Oct 3 '11 at 18:38
AskenAsken
4,47173760
answered Oct 3 '11 at 18:38
AskenAsken
4,47173760
answered Oct 3 '11 at 18:38
AskenAsken
4,47173760
4,47173760
add a comment |
add a comment |
If this is a one time thing, you can try this (not in production!).
I usually just go to the child procedure and on the query that returns the result set, add INTO YourNewTable between the column select list and the FROM. Run the child procedure 1 time and then remove the change.
You can then go and look at the result set columns by looking at YourNewTable in SQl Server Management Studio, or any table meta data query like:
SELECT
*
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME='YourNewTable'
ORDER BY ORDINAL_POSITION
answered Mar 19 '10 at 14:44
KM.KM.
82.9k27149189
add a comment |
If this is a one time thing, you can try this (not in production!).
I usually just go to the child procedure and on the query that returns the result set, add INTO YourNewTable between the column select list and the FROM. Run the child procedure 1 time and then remove the change.
You can then go and look at the result set columns by looking at YourNewTable in SQl Server Management Studio, or any table meta data query like:
SELECT
*
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME='YourNewTable'
ORDER BY ORDINAL_POSITION
answered Mar 19 '10 at 14:44
KM.KM.
82.9k27149189
add a comment |
If this is a one time thing, you can try this (not in production!).
I usually just go to the child procedure and on the query that returns the result set, add INTO YourNewTable between the column select list and the FROM. Run the child procedure 1 time and then remove the change.
You can then go and look at the result set columns by looking at YourNewTable in SQl Server Management Studio, or any table meta data query like:
SELECT
*
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME='YourNewTable'
ORDER BY ORDINAL_POSITION
answered Mar 19 '10 at 14:44
KM.KM.
82.9k27149189
If this is a one time thing, you can try this (not in production!).
I usually just go to the child procedure and on the query that returns the result set, add INTO YourNewTable between the column select list and the FROM. Run the child procedure 1 time and then remove the change.
You can then go and look at the result set columns by looking at YourNewTable in SQl Server Management Studio, or any table meta data query like:
SELECT
*
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME='YourNewTable'
ORDER BY ORDINAL_POSITION
answered Mar 19 '10 at 14:44
KM.KM.
82.9k27149189
answered Mar 19 '10 at 14:44
KM.KM.
82.9k27149189
answered Mar 19 '10 at 14:44
KM.KM.
82.9k27149189
answered Mar 19 '10 at 14:44
KM.KM.
82.9k27149189
82.9k27149189
add a comment |
add a comment |
in your stored procedure, you will need to perform a "SELECT INTO" with a "WHERE 1 = 0" so it just creates an empty table.
If you want to define the data types:
convert or cast your column into that data type
if you want a NOT NULL constraint, then you must use in the select:
isnull(yourcolumn,'123') as yourcolumn and then in the where clause put WHERE yourcolumn <> '123'
answered Mar 27 '12 at 14:59
elviselvis
207210
add a comment |
in your stored procedure, you will need to perform a "SELECT INTO" with a "WHERE 1 = 0" so it just creates an empty table.
If you want to define the data types:
convert or cast your column into that data type
if you want a NOT NULL constraint, then you must use in the select:
isnull(yourcolumn,'123') as yourcolumn and then in the where clause put WHERE yourcolumn <> '123'
answered Mar 27 '12 at 14:59
elviselvis
207210
add a comment |
in your stored procedure, you will need to perform a "SELECT INTO" with a "WHERE 1 = 0" so it just creates an empty table.
If you want to define the data types:
convert or cast your column into that data type
if you want a NOT NULL constraint, then you must use in the select:
isnull(yourcolumn,'123') as yourcolumn and then in the where clause put WHERE yourcolumn <> '123'
answered Mar 27 '12 at 14:59
elviselvis
207210
in your stored procedure, you will need to perform a "SELECT INTO" with a "WHERE 1 = 0" so it just creates an empty table.
If you want to define the data types:
convert or cast your column into that data type
if you want a NOT NULL constraint, then you must use in the select:
isnull(yourcolumn,'123') as yourcolumn and then in the where clause put WHERE yourcolumn <> '123'
answered Mar 27 '12 at 14:59
elviselvis
207210
answered Mar 27 '12 at 14:59
elviselvis
207210
answered Mar 27 '12 at 14:59
elviselvis
207210
answered Mar 27 '12 at 14:59
elviselvis
207210
207210
add a comment |
add a comment |
Have you tried this?
DECLARE @sql NVARCHAR(MAX) = N'EXEC mydb.dbo.my_procedure;';
SELECT name, system_type_name, max_length, is_nullable
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);
It won't build the table for you, but you can quickly create it knowing the names and types of the column names.
answered Nov 23 '18 at 23:07
Shafiq JethaShafiq Jetha
429716
add a comment |
Have you tried this?
DECLARE @sql NVARCHAR(MAX) = N'EXEC mydb.dbo.my_procedure;';
SELECT name, system_type_name, max_length, is_nullable
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);
It won't build the table for you, but you can quickly create it knowing the names and types of the column names.
answered Nov 23 '18 at 23:07
Shafiq JethaShafiq Jetha
429716
add a comment |
Have you tried this?
DECLARE @sql NVARCHAR(MAX) = N'EXEC mydb.dbo.my_procedure;';
SELECT name, system_type_name, max_length, is_nullable
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);
It won't build the table for you, but you can quickly create it knowing the names and types of the column names.
answered Nov 23 '18 at 23:07
Shafiq JethaShafiq Jetha
429716
Have you tried this?
DECLARE @sql NVARCHAR(MAX) = N'EXEC mydb.dbo.my_procedure;';
SELECT name, system_type_name, max_length, is_nullable
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);
It won't build the table for you, but you can quickly create it knowing the names and types of the column names.
answered Nov 23 '18 at 23:07
Shafiq JethaShafiq Jetha
429716
answered Nov 23 '18 at 23:07
Shafiq JethaShafiq Jetha
429716
answered Nov 23 '18 at 23:07
Shafiq JethaShafiq Jetha
429716
answered Nov 23 '18 at 23:07
Shafiq JethaShafiq Jetha
429716
429716
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f2477921%2fsql-server-table-definition-from-stored-procedure-result-set%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Not that I am aware of. Why would you want to?
– CResults
Mar 19 '10 at 14:19
To avoid having to manually create the table, I'm just trying to save some time really ... I've had issues in the past using 'INSERT EXEC' where the columns returned in the stored procedure don't exactly match that of the table. It can be a real pain when these procedures return so many columns.
– SuperBrook
Mar 19 '10 at 14:22