准备好了吗，一个充满CONCAT的可怕CTE即将到来...
此代码也可在我的 GitHub Gist 上找到，以备不时之需。
下面的表值函数将为 INFORMATION_SCHEMA.TABLES 中的每个表生成一个 CREATE TYPE ... AS TABLE (...) 语句。
更进一步，还有一个标量函数，它返回单个指定表和模式的字符串 CREATE TYPE 语句。
示例输出如下所示。
功能：
- 是的！，可以复制粘贴！
- 整齐地左对齐列名和类型名。
- 在注释中包括列序数，因此您可以轻松地将它们与
SqlDataReader 和 SqlDataRecord 列序数匹配。
- 基于源表的 PK 生成
PRIMARY KEY 约束，包括支持复合 PK。
- 处理
max-length 列。
- 在方括号中转义所有列名。不幸的是，T-SQL 没有“仅在必要时转义”的函数，因为我发现过多的方括号很丑陋（看着你，SSDT）。
- 由于这些都是
FUNCTION 对象，而不是 PROCEDURE 对象，因此不需要任何特殊权限即可运行，并且不可能直接对您的数据库进行任何更改（您需要将生成的 CREATE TYPE 语句复制+粘贴到新的 SSMS 编辑器中并自己运行它）。所以，nyaaaa！你不能起诉我炸了你的生产 DB。
不支持的功能：
- 不会生成表类型上的索引。
- 我没有考虑到的任何内容也不会包含在功能集中。
重要说明：
- 需要支持
STRING_AGG，这意味着您需要 SQL Server 2017 并且您的 DB 的兼容性级别为 140 或更高。
- 我有点忽略了使用
nvarchar vs. varchar 字符串与 CONCAT - 所以这是一个可能的改进。
- 您可能希望将模式从
dbo 更改为您用于不应与生产 dbo-模式对象混合的 DBA 工具的模式。
第一部分：CREATE FUNCTION dbo.ShowCreateTypeFromTables()
CREATE FUNCTION dbo.ShowCreateTypeFromTables() RETURNS TABLE
AS
RETURN
WITH
pkCols AS (
SELECT
kcu.TABLE_SCHEMA,
kcu.TABLE_NAME,
CONCAT( '[', kcu.COLUMN_NAME, ']' ) AS COLUMN_NAME,
kcu.ORDINAL_POSITION AS PK_Ordinal
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON
tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
AND
tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
WHERE
tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
),
pkDefExprs AS (
SELECT
k.TABLE_SCHEMA,
k.TABLE_NAME,
CONCAT(
CHAR(13),CHAR(10),
CHAR(13),CHAR(10),
CHAR(9),
'PRIMARY KEY ( ',
STRING_AGG( k.COLUMN_NAME, ', ' ) WITHIN GROUP ( ORDER BY k.PK_Ordinal ),
' )'
) AS PKDefinition
FROM
pkCols AS k
GROUP BY
k.TABLE_SCHEMA,
k.TABLE_NAME
),
maxColNameLengths AS (
SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME,
MAX( LEN( c.COLUMN_NAME ) + 2 ) AS MaxColumnNameLength
FROM
INFORMATION_SCHEMA.COLUMNS AS c
GROUP BY
c.TABLE_SCHEMA,
c.TABLE_NAME
),
allCols AS (
SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
LEFT( CONCAT( '[', c.COLUMN_NAME, ']', SPACE( ml.MaxColumnNameLength ) ), ml.MaxColumnNameLength ) AS ColumnNamePad,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
(
CASE
WHEN c.CHARACTER_MAXIMUM_LENGTH IS NULL THEN c.DATA_TYPE
WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN CONCAT( c.DATA_TYPE, '(max)' )
ELSE CONCAT( c.DATA_TYPE, '(', c.CHARACTER_MAXIMUM_LENGTH, ')' )
END
) AS ColumnTypeExpr,
( CASE c.IS_NULLABLE WHEN 'YES' THEN ' NULL' ELSE 'NOT NULL' END ) AS NullableExpr,
c.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON
c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND
c.TABLE_NAME = t.TABLE_NAME
INNER JOIN maxColNameLengths AS ml ON
c.TABLE_SCHEMA = ml.TABLE_SCHEMA
AND
c.TABLE_NAME = ml.TABLE_NAME
),
createTableColumnExprs AS (
SELECT
CONCAT(
CHAR(9),
allCols.ColumnNamePad, ' ',
LEFT( CONCAT( allCols.ColumnTypeExpr, SPACE(17) ), 17 ), ' ',
allCols.NullableExpr, ' ',
'/* ', ( allCols.ORDINAL_POSITION - 1 ), ' */'
) AS ColumnExpr,
allCols.*
FROM
allCols
),
createTableExprs AS (
SELECT
e.TABLE_SCHEMA,
e.TABLE_NAME,
STRING_AGG( e.ColumnExpr, CONCAT(',', CHAR(13), CHAR(10) ) ) WITHIN GROUP ( ORDER BY e.ORDINAL_POSITION ) AS ColumnExprs
FROM
createTableColumnExprs AS e
GROUP BY
e.TABLE_SCHEMA,
e.TABLE_NAME
)
SELECT
ct.TABLE_SCHEMA,
ct.TABLE_NAME,
CONCAT(
'CREATE TYPE ',
ct.TABLE_SCHEMA,
'.TypeOf_',
ct.TABLE_NAME,
' AS TABLE (',
CONCAT(CHAR(13),CHAR(10)),
ct.ColumnExprs,
ISNULL( pke.PKDefinition, '' ),
CONCAT(CHAR(13),CHAR(10)),
');',
CONCAT(CHAR(13),CHAR(10))
) AS CreateTypeExpr
FROM
createTableExprs AS ct
LEFT OUTER JOIN pkDefExprs AS pke ON
ct.TABLE_SCHEMA = pke.TABLE_SCHEMA
AND
ct.TABLE_NAME = pke.TABLE_NAME
第二部分：CREATE FUNCTION dbo.ShowCreateTypeFromTable( @schema sysname, @tableName sysname )
CREATE FUNCTION dbo.ShowCreateTypeFromTable( @schema sysname, @tableName sysname ) RETURNS nvarchar(max)
AS
BEGIN
DECLARE @stmt nvarchar(max) = NULL;
SELECT
@stmt = e.CreateTypeExpr
FROM
dbo.ShowCreateTypeFromTables() AS e
WHERE
e.TABLE_SCHEMA = @schema
AND
e.TABLE_NAME = @tableName;
RETURN @stmt;
END
第三部分：示例用法和输出：
USE AdventureWorks2017;
SELECT [dbo].[ShowCreateTypeFromTable] ( 'Production', 'Product' )
GO
CREATE TYPE Production.TypeOf_Product AS TABLE (
[ProductID] int NOT NULL ,
[Name] nvarchar(50) NOT NULL ,
[ProductNumber] nvarchar(25) NOT NULL ,
[MakeFlag] bit NOT NULL ,
[FinishedGoodsFlag] bit NOT NULL ,
[Color] nvarchar(15) NULL ,
[SafetyStockLevel] smallint NOT NULL ,
[ReorderPoint] smallint NOT NULL ,
[StandardCost] money NOT NULL ,
[ListPrice] money NOT NULL ,
[Size] nvarchar(5) NULL ,
[SizeUnitMeasureCode] nchar(3) NULL ,
[WeightUnitMeasureCode] nchar(3) NULL ,
[Weight] decimal NULL ,
[DaysToManufacture] int NOT NULL ,
[ProductLine] nchar(2) NULL ,
[Class] nchar(2) NULL ,
[Style] nchar(2) NULL ,
[ProductSubcategoryID] int NULL ,
[ProductModelID] int NULL ,
[SellStartDate] datetime NOT NULL ,
[SellEndDate] datetime NULL ,
[DiscontinuedDate] datetime NULL ,
[rowguid] uniqueidentifier NOT NULL ,
[ModifiedDate] datetime NOT NULL
PRIMARY KEY ( [ProductID] )
);
截图证明：
SELECT...INTO可用于创建表，但无法创建表类型，因此在这种情况下没有逆转。除非您需要在代码中执行此操作，否则文本替换方法是最简单的方法。 - Dan Guzman