Brace yourselves, a hideous CTE filled with CONCAT is coming...
Also available as a Gist on my GitHub, for posterity
- The Gist contains multiple files, so scroll down to
ShowCreateTypeFromTables.sql)
This Table-valued FUNCTION below will generate a CREATE TYPE ... AS TABLE (...) statement for every table in INFORMATION_SCHEMA.TABLES.
Further down, is a scalar FUNCTION that returns a single string CREATE TYPE statement for the single specified table and schema.
Example output is also shown below.
Features:
- Yes!, it's copy-and-pasteable!
- Neatly left-aligns column names and type-names.
- Includes column ordinals in comments so you can easily match them up to
SqlDataReader and SqlDataRecord column ordinals.
- Generates
PRIMARY KEY constraints based on the source table's PK, including support for composite PKs.
- Handles
max-length columns.
- Escapes all column names in square-brackets. Unfortunately T-SQL doesn't have an "escape-only-if-necessary" function, because I find excessive square-brackets hideous (looking at you, SSDT).
- As these are all
FUNCTION objects, not PROCEDURE objects, these don't require any special permissions to run, and cannot possibly make any changes to your database directly (you will need to copy+paste the generated CREATE TYPE statement into a new SSMS editor and run it yourself). So nyaaaa!, you can't sue me for nuking your production DB
Things-not-supported:
- Indexes on table-types are not generated.
- Anything I haven't thought of also won't be included in the feature-set.
Important notes:
- Requires support for
STRING_AGG which means you need SQL Server 2017 and your DB in Compatibility Level 140 or higher.
- I kinda glossed-over using
CONCAT with nvarchar vs. varchar strings - so that's a possible improvement.
- You might want to change the schema from
dbo to whatever schema you use for DBA tools that shouldn't be mingled with production dbo-schema objects.
Part 1: 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), /* CRLF \r\n */
CHAR(13),CHAR(10), /* CRLF \r\n */
CHAR(9), /* TAB \t */
'PRIMARY KEY ( ',
STRING_AGG( k.COLUMN_NAME, /*separator:*/ ', ' ) 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 /* +2 for the [] added in `allCols` below */
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), /* \t TAB */
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, /*separator:*/ 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
Part 2: 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
Part 3: Example usage and output:
USE AdventureWorks2017;
SELECT [dbo].[ShowCreateTypeFromTable] ( 'Production', 'Product' )
GO
-- Generates this:
CREATE TYPE Production.TypeOf_Product AS TABLE (
[ProductID] int NOT NULL /* 1 */,
[Name] nvarchar(50) NOT NULL /* 2 */,
[ProductNumber] nvarchar(25) NOT NULL /* 3 */,
[MakeFlag] bit NOT NULL /* 4 */,
[FinishedGoodsFlag] bit NOT NULL /* 5 */,
[Color] nvarchar(15) NULL /* 6 */,
[SafetyStockLevel] smallint NOT NULL /* 7 */,
[ReorderPoint] smallint NOT NULL /* 8 */,
[StandardCost] money NOT NULL /* 9 */,
[ListPrice] money NOT NULL /* 10 */,
[Size] nvarchar(5) NULL /* 11 */,
[SizeUnitMeasureCode] nchar(3) NULL /* 12 */,
[WeightUnitMeasureCode] nchar(3) NULL /* 13 */,
[Weight] decimal NULL /* 14 */,
[DaysToManufacture] int NOT NULL /* 15 */,
[ProductLine] nchar(2) NULL /* 16 */,
[Class] nchar(2) NULL /* 17 */,
[Style] nchar(2) NULL /* 18 */,
[ProductSubcategoryID] int NULL /* 19 */,
[ProductModelID] int NULL /* 20 */,
[SellStartDate] datetime NOT NULL /* 21 */,
[SellEndDate] datetime NULL /* 22 */,
[DiscontinuedDate] datetime NULL /* 23 */,
[rowguid] uniqueidentifier NOT NULL /* 24 */,
[ModifiedDate] datetime NOT NULL /* 25 */
PRIMARY KEY ( [ProductID] )
);
Screenshot proof:
![enter image description here]()
SELECT...INTOcan be used to create a table but not a table type so there is no reverse in this case. The text replace method is the easiest unless you need to do this in code. – Primogeniture