删除未命名的约束 [英] Dropping unnamed constraints

问题描述

我创建了一些没有明确名称的外键.

I've created some foreign keys without an explicit name.

然后我发现 SQL 生成了疯狂的名称，例如 FK__Machines__IdArt__760D22A7.猜猜它们会在不同的服务器上以不同的名称生成.

Then I've found SQL generated crazy names like FK__Machines__IdArt__760D22A7. Guess they will be generated with different names at different servers.

是否有任何不错的函数可以删除作为参数传递的表和相关字段的未命名 FK 约束?

Is there any nice function to drop the unnamed FK constraints passing as arguments the tables and the fields in question?

推荐答案

没有内置程序来完成此操作，但您可以使用 information_schema 视图中的信息构建您自己的程序.

There is not a built in procedure to accomplish this, but you can build your own using the information in the information_schema views.

基于表格的示例

Create Proc dropFK(@TableName sysname)
as
Begin

Declare @FK sysname
Declare @SQL nvarchar(4000)
Declare crsFK cursor for

select tu.Constraint_Name from 
information_schema.constraint_table_usage TU
LEFT JOIN SYSOBJECTS SO 
ON TU.Constraint_NAME = SO.NAME
where xtype = 'F'
and Table_Name = @TableName
open crsFK
fetch next from crsFK into @FK
While (@@Fetch_Status = 0)
Begin
    Set @SQL = 'Alter table ' + @TableName + ' Drop Constraint ' + @FK
    Print 'Dropping ' + @FK
    exec sp_executesql  @SQL
    fetch next from crsFK into @FK
End
Close crsFK
Deallocate crsFK
End

这篇关于删除未命名的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！