Alter user defined type in SQL Server

Question

I created few user defined types in my database as below CREATE TYPE  dbo   StringID  FROM  nvarchar  20  NOT NULL  and assigned them to various tables  The tables in my database are in various schemas  not only dbo  But I realized I need bigger field  and I need to alter  e g increase from  nvarchar  20  to  nvarchar  50   but there is no ALTER TYPE statement  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 CustID  StringID  to CustID  nvarchar 20    Drop the user type and recreate it with new type - e g  nvarchar 50  and finally set back fields to user type I do not have rules defined on types  so don t have to drop rules and re-add them  Any help is appreciated

User · Answer

I ran into this issue with custom types in stored procedures, and solved it with the script below. I didn't fully understand the scripts above, and I follow the rule of "if you don't know what it does, don't do it".

In a nutshell, I rename the old type, and create a new one with the original type name. Then, I tell SQL Server to refresh its details about each stored procedure using the custom type. You have to do this, as everything is still "compiled" with reference to the old type, even with the rename. In this case, the type I needed to change was "PrizeType". I hope this helps. I'm looking for feedback, too, so I learn :)

Note that you may need to go to Programmability > Types > [Appropriate User Type] and delete the object. I found that DROP TYPE doesn't appear to always drop the type even after using the statement.

/* Rename the UDDT you want to replace to another name */ 
exec sp_rename 'PrizeType', 'PrizeTypeOld', 'USERDATATYPE';

/* Add the updated UDDT with the new definition */ 
CREATE TYPE [dbo].[PrizeType] AS TABLE(
    [Type] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [ImageUrl] [varchar](max) NULL
);

/* We need to force stored procedures to refresh with the new type... let's take care of that. */
/* Get a cursor over a list of all the stored procedures that may use this and refresh them */
declare sprocs cursor
  local static read_only forward_only
for
    select specific_name from information_schema.routines where routine_type = 'PROCEDURE'

declare @sprocName varchar(max)

open sprocs
fetch next from sprocs into @sprocName
while @@fetch_status = 0
begin
    print 'Updating ' + @sprocName;
    exec sp_refreshsqlmodule @sprocName
    fetch next from sprocs into @sprocName
end
close sprocs
deallocate sprocs

/* Drop the old type, now that everything's been re-assigned; must do this last */
drop type PrizeTypeOld;

User · Answer

The simplest way to do this is through Visual Studio s object explorer  which is also supported in the Community edition   Once you have made a connection to SQL server  browse to the type  right click and select View Code  make your changes to the schema of the user defined type and click update  Visual Studio should show you all of the dependencies for that object and generate scripts to update the type and recompile dependencies

User · Answer

We are using the following procedure  it allows us to re-create a type from scratch  which is  a start    It renames the existing type  creates the type  recompiles stored procs and then drops the old type   This takes care of scenarios where simply dropping the old type-definition fails due to references to that type   Usage Example    exec RECREATE TYPE  schema  dbo    typ nme  typ foo    sql  AS TABLE  bar  varchar 10  NOT NULL     Code   CREATE PROCEDURE  dbo   RECREATE TYPE       schema     VARCHAR 100         -- the schema name for the existing type      typ nme    VARCHAR 128         -- the type-name  without schema name       sql        VARCHAR MAX         -- the SQL to create a type WITHOUT the  CREATE TYPE schema typename  part AS DECLARE      scid       BIGINT       typ id     BIGINT       temp nme   VARCHAR 1000        msg        VARCHAR 200  BEGIN     -- find the existing type by schema and name     SELECT  scid    SCHEMA ID  FROM sys schemas WHERE UPPER name    UPPER  schema       IF   scid IS NULL  BEGIN         SET  msg    Schema        schema       not found            RAISERROR   msg  1  0       END      SELECT  typ id   system type id FROM sys types WHERE UPPER name    UPPER  typ nme       SET  temp nme    typ nme     rcrt   -- temporary name for the existing type      -- if the type-to-be-recreated actually exists  then rename it  give it a temporary name      -- if it doesn t exist  then that s OK  too      IF   typ id IS NOT NULL  BEGIN         exec sp rename  objname  typ nme   newname   temp nme   objtype  USERDATATYPE      END           -- now create the new type     SET  sql    CREATE TYPE      schema          typ nme          sql      exec sp sqlexec  sql       -- if we are RE-creating a type  as opposed to just creating a brand-spanking-new type         IF   typ id IS NOT NULL  BEGIN         exec recompile prog     -- then recompile all stored procs  that may have used the type          exec sp droptype  typename  temp nme    -- and drop the temporary type which is now no longer referenced     END      END  GO   CREATE PROCEDURE  dbo   recompile prog  AS BEGIN     SET NOCOUNT ON      DECLARE  v TABLE  RecID INT IDENTITY 1 1   spname sysname      -- retrieve the list of stored procedures     INSERT INTO           v spname       SELECT                s  name            items name                FROM           SELECT sp name  sp schema id  sp is ms shipped FROM sys procedures sp UNION SELECT so name  so SCHEMA ID  so is ms shipped FROM sys objects so WHERE so type desc LIKE   FUNCTION    items         INNER JOIN sys schemas s ON s schema id   items schema id             WHERE is ms shipped   0       -- counter variables     DECLARE  cnt INT   Tot INT      SELECT  cnt   1      SELECT  Tot   COUNT    FROM  v      DECLARE  spname sysname     -- start the loop     WHILE  Cnt  lt    Tot BEGIN             SELECT  spname   spname                 FROM  v                 WHERE RecID    Cnt          --PRINT  refreshing        spname             BEGIN TRY        -- refresh the stored procedure                     EXEC sp refreshsqlmodule  spname             END TRY             BEGIN CATCH                     PRINT  Validation failed for        spname      Error     ERROR MESSAGE            END CATCH             SET  Cnt    cnt   1      END   END

User · Answer

This is what I normally use  albeit a bit manual      Add a  temporary  UDDT with the new definition     exec sp addtype t myudt tmp   numeric 18 5    NULL       Build a command to alter all the existing columns - cut and     paste the output  then run it     select  alter table dbo     TABLE NAME             alter column     COLUMN NAME     t myudt tmp   from INFORMATION SCHEMA COLUMNS  where DOMAIN NAME    t myudt       Remove the old UDDT     exec sp droptype t mydut      Rename the  temporary  UDDT to the correct name     exec sp rename  t myudt tmp    t myudt    USERDATATYPE

User · Answer

As devio says there is no way to simply edit a UDT if it s in use     A work-round through SMS that worked for me was to generate a create script and make the appropriate changes  rename the existing UDT  run the create script  recompile the related sprocs and drop the renamed version

User · Answer

New answer to an old question   Visual Studio Database Projects handle the drop and recreate process when you deploy changes  It will drop stored procs that use UDDTs and then recreate them after dropping and recreating the data type

User · Answer

Simple DROP TYPE first then CREATE TYPE again with corrections alterations   There is a simple test to see if it is defined before you drop it     much like a table  proc or function -- if I wasn t at work I would look what that is    I only skimmed above too     if I read it wrong I apologise in advance

User · Answer

1 Rename the old UDT  2 Execute query    3 Drop the old UDT

User · Answer

The solutions provided here can only be applied if the user defined types are used in table definitions only  and if the UDT columns are not indexed    Some developers also have SP s and functions using UDT parameters  which is not covered either   see comments on Robin s link and in the Connect entry   The Connect entry from 2007 has finally been closed after 3 years      Thank you for submitting this   suggestion  but given its priority   relative to the many other items in   our queue  it is unlikely that we will   actually complete it  As such  we are   closing this suggestion as    won   t   fix       I tried to solve a similiar problem ALTERing XML SCHEMA COLLECTIONS  and the steps seem to mostly apply to ALTER TYPE  too   To drop a UDT  the following steps are necessary    If a table column references the UDT  it has to be converted to the underlying type If the table column has a default constraint  drop the default constraint If a procedure or function has UDT parameters  the procedure or function has to be dropped If there is an index on a UDT column  the index has to be dropped If the index is a primary key  all foreign keys have to be dropped If there are computed columns based on a UDT column  the computed columns have to be dropped If there are indexes on these computed columns  the indexes have to be dropped If there are schema-bound views  functions  or procedures based on tables containing UDT columns  these objects have to be dropped

User · Answer

there s a good example of a more comprehensive script here   It s worth noting that this script will include views if you have any  I ran it and instead of exec ing inline generated a script as the output which I then tweaked and ran   Also  if you have functions sprocs using the user defeined types you ll need to drop those before running your script   Lesson Learned  in future  don t bother with UDTs they re more hassle than they re worth   SET NOCOUNT ON  DECLARE  udt VARCHAR 150  DECLARE  udtschema VARCHAR 150  DECLARE  newudtschema VARCHAR 150  DECLARE  newudtDataType VARCHAR 150  DECLARE  newudtDataSize smallint DECLARE  OtherParameter VARCHAR 50   SET  udt    Name  -- Existing UDDT SET  udtschema    dbo  -- Schema of the UDDT SET  newudtDataType    varchar  -- Data type for te new UDDT SET  newudtDataSize   500 -- Lenght of the new UDDT SET  newudtschema    dbo  -- Schema of the new UDDT SET  OtherParameter     NULL  -- Other parameters like NULL   NOT NULL DECLARE  Datatype VARCHAR 50        Datasize SMALLINT  DECLARE  varcharDataType VARCHAR 50   DECLARE  Schemaname VARCHAR 50        TableName VARCHAR 50        FiledName VARCHAR 50   CREATE TABLE  udtflds             Schemaname VARCHAR 50         TableName VARCHAR 50         FiledName VARCHAR 50         SELECT TOP 1          Datatype   Data type           Datasize   character maximum length FROM    INFORMATION SCHEMA COLUMNS WHERE   Domain name    udt         AND Domain schema    udtschema  SET  varcharDataType    Datatype IF  DataType Like   char       AND  Datasize IS NOT NULL     AND    newudtDataType  lt  gt   varchar max             OR  newudtDataType  lt  gt   nvarchar max                 BEGIN         SET  varcharDataType    varcharDataType                     CAST  Datasize AS VARCHAR 50             END  INSERT  INTO  udtflds         SELECT  TABLE SCHEMA                  TABLE NAME                  Column Name         FROM    INFORMATION SCHEMA COLUMNS         WHERE   Domain name    udt                 AND Domain schema    udtschema  DECLARE  exec VARCHAR 500   DECLARE alter cursor CURSOR     FOR SELECT  Schemaname                  TableName                  FiledName         FROM     udtflds  OPEN alter cursor FETCH NEXT FROM alter cursor INTO  Schemaname   TableName   FiledName  WHILE   FETCH STATUS   0     BEGIN         SET  exec    Alter Table      Schemaname          TableName                  ALTER COLUMN      FiledName          varcharDataType         EXECUTE    exec                          FETCH NEXT FROM alter cursor INTO  Schemaname   TableName   FiledName      END  CLOSE alter cursor  SET  exec    DROP TYPE       udtschema            udt       EXEC    exec        SET  varcharDataType    newudtDataType  IF  newudtDataType Like   char       AND  newudtDataSize IS NOT NULL     AND    newudtDataType  lt  gt   varchar max             OR  newudtDataType  lt  gt   nvarchar max                 BEGIN         SET  varcharDataType    varcharDataType                     CAST  newudtDataSize AS VARCHAR 50             END  SET  exec    CREATE TYPE       newudtschema            udt      FROM          varcharDataType          OtherParameter EXEC    exec        OPEN alter cursor FETCH NEXT FROM alter cursor INTO  Schemaname   TableName   FiledName  WHILE   FETCH STATUS   0     BEGIN         SET  exec    Alter Table      Schemaname          TableName                  ALTER COLUMN      FiledName                newudtschema                        udt               EXECUTE    exec                          FETCH NEXT FROM alter cursor INTO  Schemaname   TableName   FiledName     END  CLOSE alter cursor DEALLOCATE alter cursor SELECT    FROM     udtflds  DROP TABLE  udtflds   1  http   www sql-server-performance com 2008 how-to-alter-a-uddt  has replaced http   www sql-server-performance com faq How to alter a 20 UDDT p1 aspx

[sql] Alter user defined type in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql