在TSQL中删除列上的默认约束 [英] Drop default constraint on a column in TSQL

查看:733
本文介绍了在TSQL中删除列上的默认约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个表格,其中包含目前有效的栏位:

I have a table with a column like this that is currently live:

name NVARCHAR(128) NOT NULL DEFAULT ''

我改变这样的列使它为空: / p>

I am altering the column like this to make it nullable:

ALTER TABLE  mytable ALTER COLUMN name NVARCHAR(128) NULL

但是,在表的一个实例中名为DF_ mytable _datab__7DE4B36的默认约束仍然保留。我知道这可以避免,如果原作者命名约束。我有几个这些表的实例,但我不想手动删除每个表中的每个约束。什么是最简单和最优雅的方式删除此默认约束在Sql Server中的列,我可以统一应用于此表的每个实例?

However, the default constraint, named 'DF_mytable_datab__7DE4B36' in one instance of the table, still remains. I know this could have been avoided if the original author named the constraint. I have several of instances of these tables but I don't want to manually delete every constraint in every table I have. What is the easiest and most elegant way of dropping this default constraint on a column in Sql Server that I can uniformily apply to every instance of this table?

这是我最后使用的脚本:

This is the script that I ended up using:

DECLARE @table_id AS INT
DECLARE @name_column_id AS INT
DECLARE @sql nvarchar(255) 

-- Find table id
SET @table_id = OBJECT_ID('mytable')

-- Find name column id
SELECT @name_column_id = column_id
FROM sys.columns
WHERE object_id = @table_id
AND name = 'name'

-- Remove default constraint from name column
SELECT @sql = 'ALTER TABLE mytable DROP CONSTRAINT ' + D.name
FROM sys.default_constraints AS D
WHERE D.parent_object_id = @table_id
AND D.parent_column_id = @name_column_id
EXECUTE sp_executesql @sql

另一个可以用来完成这个任务的脚本可以在这里找到:
如何删除SQL默认约束而不知道其名称?

Another script that can be used to accomplish this can be found here: How to drop SQL default constraint without knowing its name?

谢谢,
Mohammed

Thanks, Mohammed

推荐答案

这将是如何删除约束

ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, table_name>
   DROP CONSTRAINT <default_constraint_name, sysname, default_constraint_name>
GO

使用脚本

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'dotnetnuke'
set @table = 'tabs'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @table
    exec    sp_executesql @sql
END

学分来自Jon Galloway
http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx

Credits go to Jon Galloway http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx

这篇关于在TSQL中删除列上的默认约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆