Kdtyjb

How to avoid being sexist when trying to employ someone to function in a very sexist environment?

What is the purpose of easy combat scenarios that don't need resource expenditure?

How do I add a variable to this curl command?

How to mitigate "bandwagon attacking" from players?

Where is this triangular-shaped space station from?

Where was Karl Mordo in Infinity War?

What's the purpose of these copper coils with resitors inside them in A Yamaha RX-V396RDS amplifier?

Activating a Alphanet Faucet Wallet Remotely (without tezos-client)

Eww, those bytes are gross

How do Japanese speakers determine the implied topic when none has been mentioned?

Using AWS Fargate as web server

Should I choose Itemized or Standard deduction?

How can I improve my fireworks photography?

I am on the US no-fly list. What can I do in order to be allowed on flights which go through US airspace?

Word to be used for "standing with your toes pointing out"

How to acknowledge an embarrassing job interview, now that I work directly with the interviewer?

Walking in a rotating spacecraft and Newton's 3rd Law of Motion

Do any poskim exempt 13-20-year-olds from Mussaf?

Quenching swords in dragon blood; why?

Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?

How to satisfy a player character's curiosity about another player character?

How to approximate rolls for potions of healing using only d6's?

Finding ratio of the area of triangles

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

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 16 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
  9 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 16 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
  9 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 16 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 16 hours ago

Joe Smith 8435Joe Smith 8435

666

share|improve this question

asked 16 hours ago

Joe Smith 8435Joe Smith 8435

666

asked 16 hours ago

Joe Smith 8435Joe Smith 8435

666

asked 16 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