SQL Create Script DDL Table from View

Question

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

• dean: Well, you can just SELECT * INTO aTable FROM view WHERE 1=0, and then script out aTable...

Answer 1

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