无法修改用户定义的表类型。

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.

我有一个SQL用户定义的表类型。它用于许多存储过程。现在我需要更改该表类型中的一列。我尝试删除并重新创建用户定义的表类型。但是SQL Server不允许这样做。它显示出跟随错误。

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 ?

如何修改用户定义的表类型，而不修改使用用户定义的表类型的所有存储过程?

4 个解决方案

#1

You have binding in SP_DoSomething stored procedure. The type you want to change is used in that stored procedure.

在SP_DoSomething存储过程中有绑定。要更改的类型在该存储过程中使用。

You need to save script of that procedure. Drop it. Change dbo.UserDefinedTableType and create procedure again.

您需要保存该过程的脚本。放弃。dbo变化。UserDefinedTableType并再次创建过程。

There is a similar post here. Check is some of the answers can help you. Answer of @norlando seems promising.

这里有一个类似的职位。检查是一些可以帮助你的答案。@norlando的回答似乎很有希望。

#2

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).
如果您的procs定义以alter开始，您需要添加代码和逻辑来控制它，并在游标中处理它(首先创建空proc，然后修改)。
Using it will also remove all granted rights on the procs.
使用它也将删除所有在proc上授予的权限。

...

…

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

#3

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.

您可以使用这个命令来删除和重新创建所有的SPs和函数。我建议您使用Print line运行此命令来创建Drop(s)和create (s)命令。然后可以在Drop(s)命令和Create(s)命令之间进行修改。

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

#4

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.

您可以自动执行临时删除依赖项并重新创建它们的过程，因此如果您有许多依赖项，您就不需要麻烦了。有关如何自动执行此过程的说明，请参阅我的答案。

#1