usp_CreateTypeFromTable.sql

usp_CreateTypeFromTable.sql

CREATE PROCEDURE [dbo].[usp_CreateTypeFromTable] @TableName SYSNAME,
                                                     @TypeName SYSNAME
    AS
    BEGIN
        DECLARE @sql NVARCHAR(MAX) = N'';

        SELECT @sql = @sql + N',' + CHAR(13) + CHAR(10) + CHAR(9) + 
            QUOTENAME(c.name) + ' ' + s.name +
            IIF(LOWER(s.name) LIKE '%char', 
                '(' +
                    IIF(c.max_length = -1, 'max', CONVERT(VARCHAR(12),
                        (c.max_length / (IIF(LOWER(LEFT(s.name, 1)) = N'n', 2, 1))))) + ')', 
                '')
               -- need much more conditionals here for other data types
        FROM sys.columns AS c
                 INNER JOIN sys.types AS s
                            ON c.system_type_id = s.system_type_id
                                AND c.user_type_id = s.user_type_id
        WHERE c.[object_id] = OBJECT_ID(@TableName);

        SELECT @sql = N'CREATE TYPE ' + @TypeName
            + ' AS TABLE ' + CHAR(13) + CHAR(10) + '(' + STUFF(@sql, 1, 1, '')
            + CHAR(13) + CHAR(10) + ');';

        -- print @sql;
        exec sys.sp_executesql @sql;
    END