Ryfujk

Predict mars robot position

Why do members of Congress in committee hearings ask witnesses the same question multiple times?

Using AWS Fargate as web server

Metadata API deployments are failing in Spring '19

Avoiding morning and evening handshakes

Am I a Rude Number?

Proof by Induction - New to proofs

What is better: yes / no radio, or simple checkbox?

Auto Insert date into Notepad

Obtaining a matrix of complex values from associations giving the real and imaginary parts of each element?

Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?

4 Spheres all touching each other??

Crystal compensation for temp and voltage

What happens if a wizard reaches level 20 but has no 3rd-level spells that they can use with the Signature Spells feature?

Finding the number of integers that are a square and a cube at the same time

Has the Isbell–Freyd criterion ever been used to check that a category is concretisable?

On what did Lego base the appearance of the new Hogwarts minifigs?

How would an AI self awareness kill switch work?

Eww, those bytes are gross

Why is c4 a better move in this position?

What is Crew Dragon approaching in this picture?

How can I improve my fireworks photography?

What are these green text/line displays shown during the livestream of Crew Dragon's approach to dock with the ISS?

Should I choose Itemized or Standard deduction?

SQL Create Script DDL Table from View

View in SQL Server 2008MySQL slow select from a viewBasic transactional DDL script in PostgreSQLCreate a table based on a table depending on the values in the initial table columnHow can I create a view with self referencing table and a cyclic condition?Create a view and populate 1 column with the extracted day-month from a epoch date from the main tableselect MAX() from MySQL view (2x INNER JOIN) is slowCreate View using table and column names from data in a table, then build SQLGenerate SQL Table DDL from a ViewSQL Find Code for Each Separate Column in View

2

How do I script a Create Table DDL for a View?

Example View:

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.Transaction tr

   on cust.CustomerId = tr.TransactionId

Expected Result:

create table dbo.CustomerTransaction

(    

     CustomerId int,

     CustomerName varchar(25),

     PurchaseAmount decimal(10,2)

)

sql-server database-design sql-server-2016 view ddl

share|improve this question

asked 15 hours ago

Joe Smith 8435Joe Smith 8435

666

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well, you can just SELECT * INTO aTable FROM view WHERE 1=0, and then script out aTable...
  
  – dean
  8 hours ago
  

  
  
  
  

add a comment | 

2

How do I script a Create Table DDL for a View?

Example View:

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.Transaction tr

   on cust.CustomerId = tr.TransactionId

Expected Result:

create table dbo.CustomerTransaction

(    

     CustomerId int,

     CustomerName varchar(25),

     PurchaseAmount decimal(10,2)

)

sql-server database-design sql-server-2016 view ddl

share|improve this question

asked 15 hours ago

Joe Smith 8435Joe Smith 8435

666

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well, you can just SELECT * INTO aTable FROM view WHERE 1=0, and then script out aTable...
  
  – dean
  8 hours ago
  

  
  
  
  

add a comment | 

How do I script a Create Table DDL for a View?

Example View:

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.Transaction tr

   on cust.CustomerId = tr.TransactionId

Expected Result:

create table dbo.CustomerTransaction

(    

     CustomerId int,

     CustomerName varchar(25),

     PurchaseAmount decimal(10,2)

)

sql-server database-design sql-server-2016 view ddl

share|improve this question

asked 15 hours ago

Joe Smith 8435Joe Smith 8435

666

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.Transaction tr

   on cust.CustomerId = tr.TransactionId

create table dbo.CustomerTransaction

(    

     CustomerId int,

     CustomerName varchar(25),

     PurchaseAmount decimal(10,2)

)

share|improve this question

asked 15 hours ago

Joe Smith 8435Joe Smith 8435

666

share|improve this question

asked 15 hours ago

Joe Smith 8435Joe Smith 8435

666

asked 15 hours ago

Joe Smith 8435Joe Smith 8435

666

asked 15 hours ago

Joe Smith 8435Joe Smith 8435

666

1 Answer
1

active

oldest

votes

3

Since your question tag says SQL Server 2016, you could take advantage of sys.dm_exec_describe_first_result_set which became available in SQL Server 2012.

This dynamic management function takes a Transact-SQL statement as a
parameter and describes the metadata of the first result set for the
statement.

---

--Demo set up

drop table if exists dbo.customer

drop table if exists dbo.[transaction]



create table dbo.Customer(CustomerId int, CustomerName varchar(25))

create table dbo.[Transaction](TransactionId int, PurchaseAmount decimal(10,2))

DROP VIEW IF EXISTS CustomerTransaction 

GO

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.[Transaction] tr

   on cust.CustomerId = tr.TransactionId

go

-------------------------------

--The solution    

SET NOCOUNT ON;



DECLARE @sql NVARCHAR(MAX)

    ,@cols NVARCHAR(MAX) = N'';



SELECT @cols += N',' + NAME + ' ' + system_type_name

FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerTransaction', NULL, 1);



SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

SET @sql = replace(@sql,',',',')

print @sql

---

CREATE TABLE #tmp(CustomerId int,CustomerName varchar(25),PurchaseAmount decimal(10,2));

share|improve this answer

answered 15 hours ago

Scott HodginScott Hodgin

17.6k21634

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});


}
});

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231165%2fsql-create-script-ddl-table-from-view%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

3

Since your question tag says SQL Server 2016, you could take advantage of sys.dm_exec_describe_first_result_set which became available in SQL Server 2012.

This dynamic management function takes a Transact-SQL statement as a
parameter and describes the metadata of the first result set for the
statement.

---

--Demo set up

drop table if exists dbo.customer

drop table if exists dbo.[transaction]



create table dbo.Customer(CustomerId int, CustomerName varchar(25))

create table dbo.[Transaction](TransactionId int, PurchaseAmount decimal(10,2))

DROP VIEW IF EXISTS CustomerTransaction 

GO

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.[Transaction] tr

   on cust.CustomerId = tr.TransactionId

go

-------------------------------

--The solution    

SET NOCOUNT ON;



DECLARE @sql NVARCHAR(MAX)

    ,@cols NVARCHAR(MAX) = N'';



SELECT @cols += N',' + NAME + ' ' + system_type_name

FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerTransaction', NULL, 1);



SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

SET @sql = replace(@sql,',',',')

print @sql

---

CREATE TABLE #tmp(CustomerId int,CustomerName varchar(25),PurchaseAmount decimal(10,2));

share|improve this answer

answered 15 hours ago

Scott HodginScott Hodgin

17.6k21634

add a comment | 

3

Since your question tag says SQL Server 2016, you could take advantage of sys.dm_exec_describe_first_result_set which became available in SQL Server 2012.

This dynamic management function takes a Transact-SQL statement as a
parameter and describes the metadata of the first result set for the
statement.

---

--Demo set up

drop table if exists dbo.customer

drop table if exists dbo.[transaction]



create table dbo.Customer(CustomerId int, CustomerName varchar(25))

create table dbo.[Transaction](TransactionId int, PurchaseAmount decimal(10,2))

DROP VIEW IF EXISTS CustomerTransaction 

GO

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.[Transaction] tr

   on cust.CustomerId = tr.TransactionId

go

-------------------------------

--The solution    

SET NOCOUNT ON;



DECLARE @sql NVARCHAR(MAX)

    ,@cols NVARCHAR(MAX) = N'';



SELECT @cols += N',' + NAME + ' ' + system_type_name

FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerTransaction', NULL, 1);



SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

SET @sql = replace(@sql,',',',')

print @sql

---

CREATE TABLE #tmp(CustomerId int,CustomerName varchar(25),PurchaseAmount decimal(10,2));

share|improve this answer

answered 15 hours ago

Scott HodginScott Hodgin

17.6k21634

add a comment | 

Since your question tag says SQL Server 2016, you could take advantage of sys.dm_exec_describe_first_result_set which became available in SQL Server 2012.

This dynamic management function takes a Transact-SQL statement as a
parameter and describes the metadata of the first result set for the
statement.

---

--Demo set up

drop table if exists dbo.customer

drop table if exists dbo.[transaction]



create table dbo.Customer(CustomerId int, CustomerName varchar(25))

create table dbo.[Transaction](TransactionId int, PurchaseAmount decimal(10,2))

DROP VIEW IF EXISTS CustomerTransaction 

GO

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.[Transaction] tr

   on cust.CustomerId = tr.TransactionId

go

-------------------------------

--The solution    

SET NOCOUNT ON;



DECLARE @sql NVARCHAR(MAX)

    ,@cols NVARCHAR(MAX) = N'';



SELECT @cols += N',' + NAME + ' ' + system_type_name

FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerTransaction', NULL, 1);



SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

SET @sql = replace(@sql,',',',')

print @sql

---

CREATE TABLE #tmp(CustomerId int,CustomerName varchar(25),PurchaseAmount decimal(10,2));

share|improve this answer

answered 15 hours ago

Scott HodginScott Hodgin

17.6k21634

--Demo set up

drop table if exists dbo.customer

drop table if exists dbo.[transaction]



create table dbo.Customer(CustomerId int, CustomerName varchar(25))

create table dbo.[Transaction](TransactionId int, PurchaseAmount decimal(10,2))

DROP VIEW IF EXISTS CustomerTransaction 

GO

create view dbo.CustomerTransaction

as

select

   cust.CustomerId,    -- this is int

   cust.CustomerName,  -- varchar(25)

   tr.PurchaseAmount   -- decimal(10,2)

from dbo.Customer cust

inner join dbo.[Transaction] tr

   on cust.CustomerId = tr.TransactionId

go

-------------------------------

--The solution    

SET NOCOUNT ON;



DECLARE @sql NVARCHAR(MAX)

    ,@cols NVARCHAR(MAX) = N'';



SELECT @cols += N',' + NAME + ' ' + system_type_name

FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerTransaction', NULL, 1);



SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

SET @sql = replace(@sql,',',',')

print @sql

CREATE TABLE #tmp(CustomerId int,CustomerName varchar(25),PurchaseAmount decimal(10,2));

share|improve this answer

answered 15 hours ago

Scott HodginScott Hodgin

17.6k21634

answered 15 hours ago

Scott HodginScott Hodgin

17.6k21634

answered 15 hours ago

Scott HodginScott Hodgin

17.6k21634