我需要删除一个唯一的约束，我不知道的名称 [英] I need to remove a unique constraints that I don't know the names of

问题描述

我维护的产品安装在随意升级的多个位置。唯一约束被添加到一些表，但我不知道什么名字在任何特定的实例。我所知道的是具有唯一约束的表/列名对，我想编写一个脚本来删除这些列/表组合上的任何唯一约束。

I maintain a product that is installed at multiple locations which as been haphazardly upgraded. Unique constraints were added to a number of tables, but I have no idea what the names are at any particular instance. What I do know is the table/columnname pair that has the unique constraints and I would like to write a script to delete any unique constraint on these column/table combinations.

这是SQL Server 2000及更高版本。

This is SQL Server 2000 and later. Something that works on 2000/2005/2008 would be best!

推荐答案

此脚本将生成一个 ALTER TABLE ..... DROP CONSTRAINT .... 命令，然后您可以复制+粘贴和执行（或在根据需要执行之前进行微调）以删除所有唯一约束/唯一索引： / p>

This script would generate a list of ALTER TABLE..... DROP CONSTRAINT.... commands, which you can then copy+paste and execute (or tweak before executing as needed) to drop all unique constraints / unique indices:

SELECT 
    'ALTER TABLE ' + OBJECT_NAME(so.parent_obj) + ' DROP CONSTRAINT ' + so.name
FROM sysobjects so
WHERE so.xtype = 'UQ'

适用于从2000到2008 R2的所有SQL Server版本。

I hope it should work on all SQL Server versions from 2000 to 2008 R2.

这篇关于我需要删除一个唯一的约束，我不知道的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！