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

Question

我有一个SQL User-Defined Table Type。它在许多存储的procedures.Now中使用，我需要更改该表类型中的列。我尝试删除并重新创建User-Defined Table Type.But 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.

如何在不修改使用User-Defined Table Type的所有存储过程的情况下修改User-Defined Table Type？

Answer 1

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

您需要保存该过程的脚本。放下。更改dbo.UserDefinedTableType并再次创建过程。

有一个类似的帖子here。一些答案可以帮助你。诺兰多的回答似乎很有希望。

Answer 2

总之，您应该删除使用此用户定义的表类型的所有函数和存储过程。然后，您可以删除用户定义的表类型并重新创建它。然后，您应该重新创建在前一步中删除的所有存储过程和函数。

您可以使用此命令删除并重新创建所有SPs和函数。我建议您使用打印行运行此命令，以创建Drop和create命令。然后，您可以在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

Answer 3

下面的代码虽然不完整，但应该是一个好的开始。请注意，除其他事项外：

• 您必须调整它(我使用的是用户类型，而不是表类型)并测试它。
• 它只处理程序。
• 如果proc定义以alter开头，则需要添加代码和逻辑来控制它并在游标中处理它(先创建空proc，然后再创建alter)。
• 使用它还将删除在procs上授予的所有权利。
• ..。 开始尝试: Begin表(代码nvarchar(max)，pname sysname，pschema sysname)声明@sql nvarchar(max)，@code nvarchar(max)，@pname sysname，@pschema sysname，@pschema sysname声明用于选择sp.definition、obj.name、schema_name(obj.schema_id)的sys.sql_modules作为sp内连接sys.objects作为sp内连接sys.objects作为obj on obj.object_id = sp.object_id Inner sys.dm_sql_referencing_entities ('dbo.TestType‘)，‘'TYPE')作为dep on dep.referencing_id = sp.object_id，其中obj.Type = 'P’打开cur_drop从cur_drop获取到@code、@pname、@pschema，而@@FETCH_STATUS =0开始打印'Drop '+@pname插入到@procs(代码、pname、pschema)中选择@code、@pname、@pname，@pschema Set @sql = 'Drop proc‘+ @pschema +’.‘+@pname+’Exec sp_executesql @sql从cur_drop获取到@ cur_drop，@pname，@ pschema End关闭cur_drop De分配cur_drop -- Drop创建类型声明cur_create游标用于选择代码，pname，pschema从cur_create获取到@code，@pname，@pschema当@@FETCH_STATUS =0开始打印'Create '+@pname Exec sp_executesql @代码从cur_create获取到@ cur_create，@pname，@pschema End关闭cur_create De分配cur_create提交结束尝试开始捕获回滚；扔；接住