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

• Jason A. Long: 1) Seems like an indexed view would be a far better option than a table. An indexed view has the benefits of persisted, index-able data and stays in sync with the underlying tables automatically. 2) If you absolutely MUST use a table, rather than an indexed view, you don't need to jump through hoops to create it. Just use the SELECT ... INTO dbo.TableName FROM... syntax. SQL Server will you can use CAST or CONVERT to set the data types or simply let SQL Server use the column type of the original tables.

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

• abject: For a large data warehouse view, I replaced Scott Hodgin's PRINT @sql with: PRINT CAST(@sql AS NTEXT) ... to prevent truncation of the DDL

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.

• user162241: will it print out data types for each column? see current proposed solution for code review, thanks-
• George.Palacios: No - that wasn't specified in your question - you simply asked how to create a new table from the structure of an existing view. Are you saying you need to generate a CREATE TABLE script rather than actually creating a new table?
• user162241: correct, need to generate create table script