sp_addtype (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_addtype(nvarchar @typename
, nvarchar @phystype
, varchar @nulltype
, nvarchar @owner)

MetaData:

 create procedure sys.sp_addtype  
    @typename sysname,            --  name of user-defined type  
    @phystype sysname,            --  physical system type of user-defined type  
    @nulltype varchar(8) = null,--  nullability of new type  
    @owner sysname = null        --  Owner of type (ignored)  
as  
    set nocount on  
  
    declare @isnull bit            --  default is getansinull()  
    declare @systemtype sysname   
    declare @numqual1 sysname  
    declare @numqual2 sysname               
      
    select @nulltype = lower(rtrim(@nulltype))  
    select @phystype = lower(rtrim(@phystype collate Latin1_General_CI_AS))  
    select @systemtype = @phystype  
      
    declare @stmt nvarchar(1280)  
  
    --  Warning: User types created via sp_addtype are contained in dbo schema. The @owner parameter is ignored.  
    if (@owner is null)  
        select @owner = user_name()  
    if (@owner <> N'dbo')  
        raiserror(15166, 10, 1)  
  
    --  sp_add/droptype only deal with types owned by dbo. So you need to be  
    --  db_owner, db_ddladmin or sa (covered by db_owner check) to be able   
    --  to create a type.  
    if is_member('db_owner')=0 and is_member('db_ddladmin')=0  
    begin  
        raiserror(15247, -1, -1)  
        return (1)  
    end  
  
    --  Check null status of the user type  
    --  types based on bit not-null by dflt for bckwrd-compat  
    if @systemtype = 'bit' and @nulltype is null  
                    --  If user didn't specify nullability,  
                    --  make sure it doesn't get set to nullable  
                    --  by getansinull()  
            select @nulltype = 'not null'  
  
    --   Should the user type allow null?  
    select @isnull = (case  
        when @nulltype is null then getansinull()  
        when @nulltype = 'null' then 1  
        when @nulltype in ('not null','nonull') then 0  
        end)  
    if @isnull is null  
    begin  
        raiserror(15085,-1,-1)  
        return (1)  
    end  
  
  
    --  Check that the physical type string matches expected pattern.  
    --  It should be one of <string(s)>, <string(s)>(number), or <string(s)>(number, number)  
    --  where <string(s)> when normalized into a single name should be found to be a system type.   
    --  Breakdown the @phystype into '@systemtype (@numqual1, @numqual2)'  
    if @systemtype like '_%(_%,_%)%'  
    begin          
        --  There should be no trailing string after the ')'  
        if (len(@systemtype) - charindex(')',@systemtype)) > 0  
        begin  
            raiserror(15036,-1,-1,@phystype)  
            return (1)  
        end      
  
        --  santity check the offsets before parsing  
        if     (charindex(',',@systemtype) < charindex('(',@systemtype)) or  
            (charindex(',',@systemtype) > charindex(')',@systemtype))  
        begin  
            raiserror(15036,-1,-1,@phystype)  
            return (1)          
        end  
          
        select @numqual1 = substring(@systemtype,  
            charindex('(',@systemtype) + 1,  
            charindex(',',@systemtype) - 1 - charindex('(',@systemtype))  
          
        select @numqual2 = substring(@systemtype,  
            charindex(',',@systemtype) + 1,  
            charindex(')',@systemtype) - 1 - charindex(',',@systemtype))  
              
        --  Extract typename  
        if @systemtype is not null   
            select @systemtype = substring(@systemtype, 1, charindex('(', @systemtype) - 1)  
    end  
    else if @systemtype like '_%(_%)%'  
    begin  
        --  There should be no trailing string after the ')'  
        if (len(@systemtype) - charindex(')',@systemtype)) > 0  
        begin  
            raiserror(15036,-1,-1,@phystype)  
            return (1)  
        end          
  
        --  santity check the offsets before parsing  
        if     (charindex(')',@systemtype) < charindex('(',@systemtype))  
        begin  
            raiserror(15036,-1,-1,@phystype)  
            return (1)          
        end  
          
        select @numqual1 = substring(@systemtype,  
            charindex('(',@systemtype) + 1,  
            charindex(')',@systemtype) - 1 - charindex('(',@systemtype))  
              
        --  Extract typename  
        if @systemtype is not null   
            select @systemtype = substring(@systemtype, 1, charindex('(', @systemtype) - 1)  
    end  
  
    --  Normalize typename.   
    select @systemtype = rtrim(@systemtype)  
    select @systemtype= (case @systemtype  
        when 'character' then 'char'  
        when 'character varying' then 'varchar'  
        when 'char varying' then 'varchar'  
        when 'integer' then 'int'  
        when 'dec' then 'decimal'  
        when 'binary varying' then 'varbinary'  
        when 'national character varying' then 'nvarchar'  
        when 'national char varying' then 'nvarchar'  
        when 'national character' then 'nchar'  
        when 'national char' then 'nchar'  
        when 'ncharacter varying' then 'nvarchar'  
        when 'ncharacter' then 'nchar'  
        when 'nchar varying' then 'nvarchar'  
        when 'national text' then 'ntext'  
        when 'rowversion' then 'timestamp'  
        when 'double precision' then 'float'  
        else @systemtype  
        end)  
  
    --  disallow new varchar(max) types through sp_addtype  
    if @systemtype in ('varchar', 'nvarchar', 'varbinary') and @numqual1 = 'max'  
    begin      
        raiserror(15108,-1,-1)  
        return (1)  
    end  
  
    --  disallow xml (cannot create thru CREATE TYPE either)  
    if @systemtype = 'xml'  
    begin  
        raiserror(15656,-1,-1)  
        return (1)  
    end  
  
    --  check that type qualifier(s) are numeric.  
    if (@numqual1 is not null and isnumeric(@numqual1)=0) or   
       (@numqual2 is not null and isnumeric(@numqual2)=0)  
    begin  
        raiserror(15036,-1,-1,@phystype)  
        return (1)  
    end  
      
    --   Check that physhical type exists. System physical types have   
    --   a xusertype < 256 and are owned by sys.  
    if not exists (select * from sys.types where user_type_id < 256   
        and (name collate Latin1_General_CI_AS)= @systemtype and schema_id = 4)  
    begin  
        raiserror(15036,-1,-1,@phystype)  
        return (1)  
    end  
  
    --  Reconstruct the physical type name from the validated parts  
    --  in preparation to call the CREATE TYPE statement.  
    select @phystype = @systemtype  
    if (@numqual1 is not null and @numqual2 is not null)  
        select @phystype = @phystype + '(' + RTRIM(@numqual1) + ',' + RTRIM(@numqual2) + ')'  
    else if (@numqual1 is not null)  
        select @phystype = @phystype + '(' + RTRIM(@numqual1) + ')'  
  
    --  Construct create type stmt  
    select @stmt = 'create type [dbo].' +  quotename(@typename)  
        + ' from ' + @phystype + ' '  
        + case @isnull when 1 then 'null' else 'not null' end  
  
    --  Create user defined type  
    EXEC(@stmt)  
  
    return (@@error) --  sp_addtype