從視圖生成 SQL 表 DDL

如何在一個腳本中從視圖生成表結構？只需要數據類型，我正在尋找具有某種類型的動態 sql 的表創建腳本

(1) 可以移除主鍵/約束，

(2) 不在乎是否添加了 null/not null (但是會很高興)

我在下面提出了範例問題/答案，請隨時進行程式碼審查，編輯/優化將來未包含在表中的其他數據類型，特殊情況/問題/例外可能未考慮在內。順便說一句，是否有任何開原始碼？

表 1 和表 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
)

看法：

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

預期的表創建腳本：

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

目前提出的解決方案：

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

注意：我只對錶結構 DDL 感興趣，對數據 DML 不感興趣

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

由於您的問題標籤顯示 SQL Server 2016，因此您可以利用SQL Server 2012 中提供的sys.dm_exec_describe_first_result_set 。

此動態管理函式將 Transact-SQL 語句作為參數，並描述該語句的第一個結果集的元數據。

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

---

結果：

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

引用自：https://dba.stackexchange.com/questions/223512