2

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

  • 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. – Jason A. Long Nov 27 '18 at 9:36
5

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));
  • thanks, this is great, some of our servers use 2016, some users 2012, I accepted answer and gave points, should I create new question for below 2016 without use of sys.dm_exec_describe_first_result_set ? – user162241 Nov 27 '18 at 21:31
  • I just tested this on a SQL Server 2012 instance and it worked (except for the DROP IF EXISTS code which came in 2016). I updated my answer to reflect that sys.dm_exec_describe_first_result_set became available in SQL Server 2012, – Scott Hodgin Nov 27 '18 at 21:40
3

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.

  • not interested in data, just table structure DDL – user162241 Nov 27 '18 at 9:36
  • 1
    @knightbob472 if you notice the SELECT TOP 0, you won't be retrieving any data using this code - try it! – George.Palacios Nov 27 '18 at 9:39
  • will it print out data types for each column? see current proposed solution for code review, thanks- – user162241 Nov 27 '18 at 9:40
  • 4
    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? – George.Palacios Nov 27 '18 at 9:41
  • correct, need to generate create table script – user162241 Nov 27 '18 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy