Menu
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 ?
367
The only way to alter a User Defined Data Type is to drop the existing and create a new one. Modifying the existing User Defined Data Type is prohibited as it may violate the validity of existing dependent data.
Since the advent of table-valued parameters in SQL Server 2008, table types have become more and more popular. Unfortunately, once a table type is actively being referenced by one or more objects, it is cumbersome to change. There is no ALTER TYPE, and you can't drop and re-create a type that is in use.
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.
52
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
