Generate SQL Table DDL from a View

Question

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

https://stackoverflow.com/questions/7254380/how-to-find-the-derived-column-types-of-a-view-in-sql-server-2005

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].[Customers]
CREATE TABLE [dbo].[Customers](
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [ContactName] [nvarchar](30) NULL,
    [ContactTitle] [nvarchar](30) NULL,
    [Address] [nvarchar](60) NULL,
    [City] [nvarchar](15) NULL,
    [Region] [nvarchar](15) NULL,
    [PostalCode] [nvarchar](10) NULL,
    [Country] [nvarchar](15) NULL,
    [Phone] [nvarchar](24) NULL,
    [Fax] [nvarchar](24) NULL
) ON [PRIMARY]
GO

DROP VIEW IF EXISTS CustomerView 
GO
CREATE VIEW CustomerView
AS
SELECT *
FROM dbo.Customers
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.CustomerView', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');
SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
SET @sql = replace(@sql,',',',' + char(10))
print @sql

---

Results:

CREATE TABLE #tmp(CustomerID nchar(5),
CompanyName nvarchar(40),
ContactName nvarchar(30),
ContactTitle nvarchar(30),
Address nvarchar(60),
City nvarchar(15),
Region nvarchar(15),
PostalCode nvarchar(10),
Country nvarchar(15),
Phone nvarchar(24),
Fax nvarchar(24));

Answer 2

I would utilise SELECT INTO.

SELECT TOP 0 *
INTO #SomeNewTable
FROM [SomeDB].[SomeSchema].[SomeView]

The above will create a new table with the same structure as the source view.