CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2012user-defined-types
Aslam Jiffry
Aslam Jiffry

Reputation: 1316

Unable to Modify User-Defined Table Type

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 ?

Upvotes: 6

Views: 24987

Answers (4)

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

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.

Upvotes: 8

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12565

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

Upvotes: 2

BornToCode
BornToCode

Reputation: 10213

You could automate the process of temporary deleting the dependencies and then re-creating them, so you shouldn't bother if you have many dependencies. For insrutctions on how to automate this process, see my answer here.

Upvotes: 0

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

The code below while incomplete should be a good start. Please note that among many other things:

  • you must adapt it (I am using user type, not table type) and test it.
  • It only handles procs.
  • If your procs definition start with alter, you need to add code and logic to control this and deal with it in the cursor (create empty proc first then alter).
  • Using it will also remove all granted rights on the procs.

  • ...

    Begin Try 
Begin Tran
    Declare @procs Table(code nvarchar(max), pname sysname, pschema sysname)
Declare @sql nvarchar(max), @code nvarchar(max), @pname sysname, @pschema sysname
Declare cur_drop Cursor For
    Select sp.definition, obj.name, schema_name(obj.schema_id) From sys.sql_modules as sp
    Inner Join sys.objects as obj on obj.object_id = sp.object_id
    Inner Join sys.dm_sql_referencing_entities ('dbo.TestType', 'TYPE') as dep on dep.referencing_id = sp.object_id
    Where obj.Type = 'P'
Open cur_drop
Fetch Next From cur_drop Into @code, @pname, @pschema

While @@FETCH_STATUS = 0
Begin
    Print 'Drop '+@pname 
    Insert into @procs(code, pname, pschema) Select @code, @pname, @pschema

    Set @sql = 'Drop proc ['+@pschema+'].['+@pname+']'
    Exec sp_executesql @sql

    Fetch Next From cur_drop Into @code, @pname, @pschema
End
Close cur_drop
Deallocate cur_drop

-- Drop Type
-- Create Type

Declare cur_create Cursor For
    Select code, pname, pschema From @procs
Open cur_create
Fetch Next From cur_create Into @code, @pname, @pschema

While @@FETCH_STATUS = 0
Begin
    Print 'Create '+@pname 
    Exec sp_executesql @code
    Fetch Next From cur_create Into @code, @pname, @pschema
End
Close cur_create
Deallocate cur_create

Commit
End Try
Begin Catch
    rollback;
    throw;
End Catch

Upvotes: 1

Related Questions