Unable to Modify User-Defined Table Type

Question

I have a SQL User-Defined Table Type. It used in many stored procedures.Now i need to change a column in that table type. I tried to drop and recreate the User-Defined Table Type.But SQL Server doesn't Allow that. It shows up following error.

Msg 3732, Level 16, State 1, Line 3
Cannot drop type 'dbo.UserDefinedTableType' because it is being referenced by object 'SP_DoSomething'. There may be other objects that reference this type.
Msg 219, Level 16, State 1, Line 3
The type 'dbo.UserDefinedTableType' already exists, or you do not have permission to create it.

How to alter the User-Defined Table Type without modifying all the Stored procedure that uses User-Defined Table Type ?

Solution 1

You have binding in SP_DoSomething stored procedure. The type you want to change is used in that stored procedure.

You need to save script of that procedure. Drop it. Change dbo.UserDefinedTableType and create procedure again.

There is a similar post here. Check is some of the answers can help you. Answer of @norlando seems promising.

Solution 2

In total you should delete all Functions and Stored Procedures which use this User-Defined Table Type. Then you can drop User-Defined Table Type and recreate it. Then you should recreate all Stored Procedures and Functions which you deleted in previous step.

You can use this command for drop and recreate all SPs and Functions. I suggest you to run this command with Print line to create Drop(s) and Create(s) command. Then you can put between Drop(s) command and Create(s) command your modification.

Declare @fullObjectName NVarChar(1000) = 'ref.Employee'

Declare @CreateCommand VarChar(Max), @DropCommand VarChar(Max)
Declare @ProcList Table 
(  
    RowId Int,
    CreateCommand NVarChar(Max),
    DropCommand NVarChar(Max)
)
Insert Into @ProcList
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(m.object_id)) RowId, 
    definition As CreateCommand,
    'DROP ' +
                        CASE OBJECTPROPERTY(referencing_id, 'IsProcedure')
                        WHEN 1 THEN 'PROC '
                        ELSE
                                CASE
                                        WHEN OBJECTPROPERTY(referencing_id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsInlineFunction') = 1 THEN 'FUNCTION '
                                        ELSE ''
                                END
                        END
                + SCHEMA_NAME(o.schema_id) + '.' +
                + OBJECT_NAME(m.object_id)  As DropCommand  
FROM sys.sql_expression_dependencies d
JOIN sys.sql_modules m 
    ON m.object_id = d.referencing_id
JOIN sys.objects o 
    ON o.object_id = m.object_id
WHERE referenced_id = TYPE_ID(@fullObjectName)

-----
Declare cur_drop SCROLL Cursor For Select CreateCommand, DropCommand From @ProcList
OPEN cur_drop
Fetch Next From cur_drop Into @CreateCommand, @DropCommand 
While @@FETCH_STATUS = 0
Begin
        --Exec sp_executesql @DropCommand
        PRINT @DropCommand
        Fetch Next From cur_drop Into @CreateCommand, @DropCommand 
End

/*
Drop And ReCreate User Defined Table Type
*/

Fetch First From cur_drop Into @CreateCommand, @DropCommand 
While @@FETCH_STATUS = 0
Begin
        --Exec sp_executesql @CreateCommand
        PRINT @CreateCommand
        Fetch Next From cur_drop Into @CreateCommand, @DropCommand 
End

Close cur_drop
Deallocate cur_drop

Solution 3

The code below while incomplete should be a good start. Please note that among many other things:

• you must adapt it (I am using user type, not table type) and test it.
• It only handles procs.
• If your procs definition start with alter, you need to add code and logic to control this and deal with it in the cursor (create empty proc first then alter).
• Using it will also remove all granted rights on the procs.

• ...

  Begin Try 
  Begin Tran
      Declare @procs Table(code nvarchar(max), pname sysname, pschema sysname)
  Declare @sql nvarchar(max), @code nvarchar(max), @pname sysname, @pschema sysname
  Declare cur_drop Cursor For
      Select sp.definition, obj.name, schema_name(obj.schema_id) From sys.sql_modules as sp
      Inner Join sys.objects as obj on obj.object_id = sp.object_id
      Inner Join sys.dm_sql_referencing_entities ('dbo.TestType', 'TYPE') as dep on dep.referencing_id = sp.object_id
      Where obj.Type = 'P'
  Open cur_drop
  Fetch Next From cur_drop Into @code, @pname, @pschema
  
  While @@FETCH_STATUS = 0
  Begin
      Print 'Drop '+@pname 
      Insert into @procs(code, pname, pschema) Select @code, @pname, @pschema
  
      Set @sql = 'Drop proc ['+@pschema+'].['+@pname+']'
      Exec sp_executesql @sql
  
      Fetch Next From cur_drop Into @code, @pname, @pschema
  End
  Close cur_drop
  Deallocate cur_drop
  
  -- Drop Type
  -- Create Type
  
  Declare cur_create Cursor For
      Select code, pname, pschema From @procs
  Open cur_create
  Fetch Next From cur_create Into @code, @pname, @pschema
  
  While @@FETCH_STATUS = 0
  Begin
      Print 'Create '+@pname 
      Exec sp_executesql @code
      Fetch Next From cur_create Into @code, @pname, @pschema
  End
  Close cur_create
  Deallocate cur_create
  
  Commit
  End Try
  Begin Catch
      rollback;
      throw;
  End Catch