how to drop uddt if used in any function or stored procedure.

rajesh yadav 171 Reputation points
2021-04-08T11:57:17.893+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,708 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,126 Reputation points
    2021-04-08T13:44:54.15+00:00

    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.

    0 comments
  2. 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.

    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.

    0 comments