title: Alter user defined type in SQL Server
body: <p>I created few user defined types in my database as below</p>
  <pre><code>CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL
  </code></pre>
  <p>and assigned them to various tables. The tables in my database are in various schemas (not only <code>dbo</code>)</p>
  <p>But I realized I need bigger field, and I need to alter, e.g increase from <code>[nvarchar](20)</code> to <code>[nvarchar](50)</code>, but there is no <code>ALTER TYPE</code> statement.</p>
  <p>I need a script that uses a temp table/cursor whatever and saves all the tables and fields where my type is used. Then change existing fields to base type - e.g. from <code>CustID [StringID]</code> to <code>CustID [nvarchar(20)]</code>.
  Drop the user type and recreate it with new type - e.g. <code>nvarchar(50)</code>
  and finally set back fields to user type</p>
  <p>I do not have rules defined on types, so don't have to drop rules and re-add them.</p>
  <p>Any help is appreciated.</p>