question

rajeshyadav-0048 avatar image
0 Votes"
rajeshyadav-0048 asked ·

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.

sql-server-generalsql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @rajeshyadav-0048,

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ·

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.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @rajeshyadav-0048,

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.

  1. Use sp_rename to rename the table type, I typically just add z to the beginning of the name.

  2. Create a new table type with the original name and any modification you need to make to the table type.

  3. Step through each dependency and run sp_refreshsqlmodule on it.

  4. 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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.