表值类型生成

declare @TableType varchar(max) =''
declare @tablename varchar(max)
set @tablename='student'
 
select @TableType=@TableType
    +case when types.name in ('varchar','nvarchar','char','nchar','varbinary')
          then '['+columns.name+']'+' '+types.name+'('
               +cast(columns.max_length as varchar)+')'+','+char(13)
          else case when types.name in ('numeric','decimal')
                    then '['+columns.name+']'+' '+types.name+'('
                         +cast(columns.max_length as varchar)+','
                         +cast(columns.scale as varchar)+')'+','+char(13)
                    else '['+columns.name+']'+' '+types.name+','+char(13)
               end
     end
  from sys.columns
    join sys.objects
    on columns.object_id=objects.object_id
    join sys.types
    on columns.user_type_id=types.user_type_id
  where columns.object_id=object_id(@tablename)
 
 
print 'CREATE TYPE '+@tablename+'Type AS TABLE
('++char(13)+left(@TableType,len(@TableType)-2)+char(13)+')'