You need to create a new user defined table type, change all of the objects that reference the old type and then drop the old one. Take a look at "How to Alter User Defined Table Type in SQL Server". Hope that gives a help.
how to drop uddt if used in any function or stored procedure.
hi,
i have one uddt ex
CREATE TYPE [dbo].[udttR] AS TABLE(
[ProjectId] [bigint] NULL
)
now the problem is it is used in many function and stored procedure and i have to drop all function and stored procedure if i have to change any thing in uddtR
so is there any way exists where uddt can be droped with out dropping the functions and stored procedure.
2 answers
Sort by: Most helpful
-
-
MelissaMa-MSFT 24,176 Reputation points
2021-04-09T02:31:18.313+00:00 Hi @rajesh yadav ,
Welcome to Microsoft Q&A!
You could refer Guoxiong's article and follow the steps one by one.
I found another method in this forum and you could also have a try.
One note is the sp_refreshsqlmodule will fail if the change you made to the table type is a breaking change to that object, typically a procedure.
- Use sp_rename to rename the table type, I typically just add z to the beginning of the name.
- Create a new table type with the original name and any modification you need to make to the table type.
- Step through each dependency and run sp_refreshsqlmodule on it.
- Drop the renamed table type.
I also had a try from my side as below:
CREATE TYPE [dbo].[udttR] AS TABLE( [ProjectId] [bigint] NULL ) CREATE PROCEDURE dbo.MyProcedure1 @tvp dbo.[udttR] READONLY AS BEGIN SELECT [ProjectId] FROM @tvp; END GO CREATE PROCEDURE dbo.MyProcedure2 AS BEGIN DECLARE @tvp dbo.[udttR]; END GO EXEC sys.sp_rename 'dbo.udttR', 'zudttR'; CREATE TYPE [dbo].[udttR] AS TABLE( [ProjectId] [int] NULL ) DECLARE @Name NVARCHAR(776); DECLARE REF_CURSOR CURSOR FOR SELECT referencing_schema_name + '.' + referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.udttR', 'TYPE'); OPEN REF_CURSOR; FETCH NEXT FROM REF_CURSOR INTO @Name; WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sys.sp_refreshsqlmodule @name = @Name; FETCH NEXT FROM REF_CURSOR INTO @Name; END; CLOSE REF_CURSOR; DEALLOCATE REF_CURSOR; GO DROP TYPE dbo.zudttR; GO
After of all, before it could be destructive to your database, you should test this on a development environment firstly.
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.