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