IF (OBJECT_ID('FK_ChannelPlayerSkins_Channels') IS NOT NULL)

只是一些需要注意的东西......

在SQL Server 2008 R2 SSMS中，“Script Constraint as -> DROP And CREATE To”命令会生成如下所示的T-SQL

USE [MyDatabase]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DEF_Detail_IsDeleted]') AND type = 'D')
BEGIN
ALTER TABLE [Patient].[Detail] DROP CONSTRAINT [DEF_Detail_IsDeleted]
END

GO

USE [MyDatabase]
GO

ALTER TABLE [Patient].[Detail] ADD  CONSTRAINT [DEF_Detail_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

开箱即用时，该脚本不会删除约束，因为SELECT返回0行。(见Microsoft Connect)。

默认约束的名称是错误的，但我认为这也与OBJECT_ID函数有关，因为更改名称并不能解决问题。

为了解决这个问题，我删除了OBJECT_ID的使用，而是使用默认的约束名称。

(SELECT * FROM dbo.sysobjects WHERE [name] = (N'DEF_Detail_IsDeleted') AND type = 'D')

我使用它来检查和远程列上的约束。它应该有你需要的一切。

DECLARE
  @ps_TableName VARCHAR(300)
  , @ps_ColumnName VARCHAR(300)

SET @ps_TableName = 'mytable'
SET @ps_ColumnName = 'mycolumn'

DECLARE c_ConsList CURSOR LOCAL STATIC FORWARD_ONLY FOR
    SELECT
    'ALTER TABLE ' + RTRIM(tb.name) + ' drop constraint ' + sco.name AS csql
    FROM
        sys.Objects tb
        INNER JOIN sys.Columns tc on (tb.Object_id = tc.object_id)
        INNER JOIN sys.sysconstraints sc ON (tc.Object_ID = sc.id and tc.column_id = sc.colid)
        INNER JOIN sys.objects sco ON (sc.Constid = sco.object_id)
    where
        tb.name=@ps_TableName
        AND tc.name=@ps_ColumnName
OPEN c_ConsList
FETCH c_ConsList INTO @ls_SQL
WHILE (@@FETCH_STATUS = 0) BEGIN

    IF RTRIM(ISNULL(@ls_SQL, '')) <> '' BEGIN
        EXECUTE(@ls_SQL)
    END
    FETCH c_ConsList INTO @ls_SQL
END
CLOSE c_ConsList
DEALLOCATE c_ConsList

IF EXISTS(SELECT TOP 1 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(N'[dbo].[ChannelPlayerSkins]') AND name = 'FK_ChannelPlayerSkins_Channels')
BEGIN
    DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
END
GO

SELECT tabla.name as Tabla,

        restriccion.name as Restriccion, 
        restriccion.type as Tipo, 
        restriccion.type_desc as Tipo_Desc
FROM {DATABASE_NAME}.sys.objects tabla 

INNER JOIN {DATABASE_NAME}.sys.objects restriccion

ON tabla.object_id = restriccion.parent_object_id

WHERE tabla.type = 'U' - Solo tablas creadas por el usuario.

AND restriccion.type = 'UQ' --Tipo de Restriccion UNIQUE

ORDER BY tabla.name, restriccion.type_desc                

在创建约束之前，我使用以下查询检查现有约束。

IF (NOT EXISTS(SELECT 1 FROM sysconstraints WHERE OBJECT_NAME(constid) = 'UX_CONSTRAINT_NAME' AND OBJECT_NAME(id) = 'TABLE_NAME')) BEGIN
...
END

这个函数以给定的表名为目标，通过名称查询约束。希望这能有所帮助。