Background

We can use a user-defined table type to declare table-valued parameters for stored procedures, functions or in a batch. However, we cannot alter/modify the type once it is created. As per Microsoft

In the restriction section, it says: ‘The user-defined table type definition cannot be modified after it is created’.

But sometimes we need to modify or alter the user defined types.

Problem with the traditional approach

A usual approach for this purpose is to delete the type and recreate along with the changes. But there is a problem when you have references. You can’t delete a type unless all the references are deleted. So one popular way to solve this is to find all the references, delete the references, delete the type, then deploy the modified type and finally restore the references from backup.

Here are the steps:

1.       First step is to find out all the references:

DECLARE @typeName varchar(500) = 'myType'

SELECT

    referencing_entity_name AS spName

  FROM sys.dm_sql_referencing_entities('dbo.'+@typeName, 'TYPE')

Using this script we can find all the references of ‘myType’. List-out the reference names and find out the backup scripts which are required to recreate them. Now the next step is to delete all the references.

2.       To delete all the references we can use a cursor. Here is the script:

DECLARE dropCursor CURSOR FOR

  SELECT

           referencing_entity_name

  FROM sys.dm_sql_referencing_entities('dbo.'+@typeName, 'TYPE')

  OPEN dropCursor

  FETCH NEXT FROM dropCursor INTO @procName

  WHILE @@FETCH_STATUS = 0

  BEGIN

    EXEC ('drop procedure [' + @procName + ']')

    FETCH NEXT FROM dropCursor INTO @procName

  END

  CLOSE dropCursor

  DEALLOCATE dropCursor

3.       Drop the Type and recreate with the modified script.

4.       Recreate all the references from the backup script.

This is troublesome and manual attention is needed to check all these things.

Alternative automatic approach

We can apply a slight modified technique to reduce all these hassles and do all the backend staffs automatically.  Here are the steps:

1.       We will follow the step 1 to find out all the reference names along with the definitions. Then we will put them in a temporary table.

DECLARE @typeName varchar(500) = 'myType'

IF TYPE_ID(@typeName) IS NOT NULL

---------Find refereced objects and put them in a temporary table---------

 SELECT

pr.name AS spName,

    mod.definition AS spText

       INTO #spBackupRestore

  FROM sys.procedures pr

  INNER JOIN sys.sql_modules mod

    ON pr.object_id = mod.object_id

  WHERE pr.Is_MS_Shipped = 0

  AND pr.name IN (SELECT

    referencing_entity_name AS spName

  FROM sys.dm_sql_referencing_entities('dbo.'+@typeName, 'TYPE'))

  -----------End-----------------

2.       Drop the references

       DECLARE @procName varchar(500);

       DECLARE @sql nvarchar(max)

       DECLARE dropCursor CURSOR FOR

       SELECT

       spName

       FROM #spBackupRestore

       OPEN dropCursor

       FETCH NEXT FROM dropCursor INTO @procName

       WHILE @@FETCH_STATUS = 0

       BEGIN

       EXEC ('drop procedure [' + @procName + ']')

       FETCH NEXT FROM dropCursor INTO @procName

       END

       CLOSE dropCursor

       DEALLOCATE dropCursor

3.       Drop the Type.

DROP TYPE myType

4.       Recreate the type with modifications

CREATE TYPE myType as TABLE(

Param1        datatype

Param2        datatype

)

5.       Restore the references:

DECLARE @sqlRestore nvarchar(max)

       DECLARE restoreCursor CURSOR FOR

       SELECT

              spText

       FROM #spBackupRestore

       OPEN restoreCursor

       FETCH NEXT FROM restoreCursor INTO @sqlRestore

       WHILE @@FETCH_STATUS = 0

       BEGIN

       SET @sqlRestore = REPLACE(@sqlRestore, '''', '''''')

       SET @sqlRestore = 'EXEC(''' + @sqlRestore + ''')'

       EXEC (@sqlRestore)

       FETCH NEXT FROM restoreCursor INTO @sqlRestore

       END

       CLOSE restoreCursor

      DEALLOCATE restoreCursor

6.       Drop the temporary table.

DROP TABLE spBackupRestore

Now putting these all together here is one single script to do all these:

DECLARE @typeName varchar(500) = 'myType'

IF TYPE_ID(@typeName) IS NOT NULL

BEGIN

---------Find referenced objects and put them in a temporary table---------

  SELECT

    pr.name AS spName,

    mod.definition AS spText

       INTO #spBackupRestore

  FROM sys.procedures pr

  INNER JOIN sys.sql_modules mod

    ON pr.object_id = mod.object_id

  WHERE pr.Is_MS_Shipped = 0

  AND pr.name IN (SELECT

    referencing_entity_name AS spName

  FROM sys.dm_sql_referencing_entities('dbo.'+@typeName, 'TYPE'))

  -----------End-----------------

  ---------Drop the referenced objects-----------------

  DECLARE @procName varchar(500);

  DECLARE @sql nvarchar(max)

  DECLARE dropCursor CURSOR FOR

  SELECT

    spName

  FROM #spBackupRestore

  OPEN dropCursor

  FETCH NEXT FROM dropCursor INTO @procName

  WHILE @@FETCH_STATUS = 0

  BEGIN

    EXEC ('drop procedure [' + @procName + ']')

    FETCH NEXT FROM dropCursor INTO @procName

  END

  CLOSE dropCursor

  DEALLOCATE dropCursor

  -----------End-----------------

END

-----------Drop user defined type-----------------

BEGIN

DROP TYPE myType

CREATE TYPE myType as TABLE(

Param1        datatype

Param2        datatype

)

END

-----------End-----------------

-----------Restore referenced objects from temporary table-----------------

BEGIN

  DECLARE @sqlRestore nvarchar(max)

  DECLARE restoreCursor CURSOR FOR

  SELECT

    spText

  FROM #spBackupRestore

  OPEN restoreCursor

  FETCH NEXT FROM restoreCursor INTO @sqlRestore

  WHILE @@FETCH_STATUS = 0

  BEGIN

    SET @sqlRestore = REPLACE(@sqlRestore, '''', '''''')

    SET @sqlRestore = 'EXEC(''' + @sqlRestore + ''')'

    EXEC (@sqlRestore)

    FETCH NEXT FROM restoreCursor INTO @sqlRestore

  END

  CLOSE restoreCursor

  DEALLOCATE restoreCursor

END

------Drop temporary table------------

DROP TABLE #spBackupRestore

GO

Now we can use this script as a template for any user type only modifying the section described in steps 3 & 4.