How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql
(1) can remove primary key/constraint,
(2) don't care if null/not null added (would be nice to have however)
I have proposed sample question/answer below, feel free to code review, edit/optimize for other data types not included in table in future, special cases/issues/exceptions may have not accounted for. Is there any open source code for this by the way?
Table 1 and 2:
create table dbo.Customer
(
CustomerId int primary key,
CustomerName varchar(255),
ZipCode varchar(9)
)
create table dbo.CustomerTransaction
(
CustomerTransactionId int primary key identity(1,1),
CustomerId int,
SalesAmount numeric (10,2),
PurchaseDate datetime
)
View:
create view dbo.CustomerTransactionVw
as
select
ct.CustomerTransactionId,
ct.SalesAmount,
ct.PurchaseDate,
cust.CustomerId,
CustomerName,
cust.ZipCode
from dbo.CustomerTransaction ct
inner join dbo.Customer cust
on cust.CustomerId = ct.CustomerId
Intended Table Create Script:
create table dbo.CustomerTransactionBigTable
(
CustomerTransactionId int identity(1,1),
CustomerId int,
SalesAmount numeric (10,2),
PurchaseDate datetime,
CustomerId int,
CustomerName varchar(255),
ZipCode varchar(9)
)
Current Proposed Solution:
declare @TableCode varchar(max) = 'create table dbo.CustomerLargeTable
( ' +
(select STUFF((
SELECT ',
'
+ c.name + ' ' +
case
when t.name like '%char%' then t.name + '(' + cast(c.max_length as varchar(10)) + ')'
when t.name like '%numeric%' or t.name like '%decimal%' then t.name + '(' + cast(c.precision as varchar(10)) + ',' + cast(c.scale as varchar(10)) + ')'
else t.name
end
FROM .sys.columns c
inner JOIN sys.types t
on t.user_type_id = c.user_type_id
and t.system_type_id = c.system_type_id
where c.object_id = object_id('CustomerTransactionVw') and is_identity = 0
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))
+ '
)'
print @TableCode
Note: I am only interested in table structure DDL, not data DML
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.
Results: