Sql-server – SQL Create Script DDL Table from View

ddlsql serversql-server-2016view

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

Best Answer

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