Script User-Defined Data Types

Continuing with the “database migration using scripting” series, this week I am publishing a script that’ll script user-defined data types. The script reads columns from the sys.types and sys.schemas DMVs to build the DROP TYPE and CREATE TYPE statements. Special attention is given to UDTs based on character, decimal, numeric, and varbinary data types since these data types require additional properties which define the maximum data size allowed.

The script is based losely on the following query:

SELECT ss.[name] AS [Schema], st.[name] AS [Name], bs.[name] AS [Type]
FROM sys.types st
    INNER JOIN sys.schemas ss ON st.[schema_id] = ss.[schema_id]
    INNER JOIN sys.types bs ON bs.[user_type_id] = st.[system_type_id]
WHERE st.[is_user_defined] = 1 -- exclude system types
ORDER BY st.[name], ss.[name]

The complete script works with SQL Server 2005 and later versions and can be downloaded from here: script-user-defined-data-types.sql.