sql-server - sql添加列默认值 - sql添加多列




SQL Server 2005删除列与约束 (9)

你是什​​么意思随机产生的? 你可以查看管理工作室中特定列的约束,或者通过sys.tables视图来查找名称是什么。

然后,您可以在删除列之前更改脚本以删除约束。 这是什么类型的约束? 如果是外键约束,请确保这样做不会损害数据库中的数据完整性。

我有一个“DEFAULT”约束列。 我想创建一个脚本来删除该列。

问题是它返回这个错误:

Msg 5074, Level 16, State 1, Line 1  
The object 'DF__PeriodSce__IsClo__4BCC3ABA' is dependent on column 'IsClosed'. 
Msg 4922, Level 16, State 9, Line 1 
ALTER TABLE DROP COLUMN IsClosed failed because one or more objects access this column.

我找不到一个简单的方法来删除一个列及其所有相关的约束(只发现大型脚本,查看系统表...那里必须(!!)是一个“好”的方式来做到这一点)。

而且由于DEFAULT约束的名字是随机生成的,我不能通过名字来删除它。

更新
约束类型是“DEFAULT”。

我看到你们提出的解决方案,但是我发现他们都很“脏”......你不觉得吗? 我不知道是用Oracle还是MySQL,但是可以这样做:

DROP COLUMN xxx CASCADE CONSTRAINTS 

它会删除所有相关的约束...或者至少它会自动删除映射到该列的约束(至少是CHECK约束!)

MSSQL中没有这样的东西吗?


您可以通过查询information_schema系统视图来获取约束名称。

select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

> select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

这不是正确的解决方案,因为它在这里解释: http : //msdn.microsoft.com/en-us/library/aa175912.aspx说:

不幸的是,列默认约束的名称不保存在ANSI COLUMNS视图中,因此您必须返回到系统表以查找名称

我发现获取DEFAULT约束的唯一方法是请求:

select  
    t_obj.name              as TABLE_NAME
    ,c_obj.name             as CONSTRAINT_NAME
    ,col.name               as COLUMN_NAME

from    sysobjects  c_obj
join    sysobjects  t_obj on c_obj.parent_obj = t_obj.id  
join    sysconstraints con on c_obj.id  = con.constid
join    syscolumns  col on t_obj.id = col.id
            and con.colid = col.colid
where
    c_obj.xtype = 'D'

我是唯一一个发现它疯了 ,无法轻松删除一个约束,只涉及我试图放下的列?
我需要执行3个连接的请求,只是为了获得名称...


只需为表格生成脚本。 在那里你可以找到所有约束的名字。


也许它可以帮助更多:

declare @tablename nvarchar(200)
declare @colname nvarchar(200)
declare @default sysname, @sql nvarchar(max)

set @tablename = 'your table'
set @colname = 'column to drop'

select @default = name 
from sys.default_constraints 
where parent_object_id = object_id(@tablename)
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id(@tablename)
    and name = @colname 
    )

set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
exec sp_executesql @sql

set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
exec sp_executesql @sql

只需要设置@tablename&@colname变量来删除列。


我也认为这是在SQL服务器没有级联下降可用的缺点。 我通过查询系统表来解决这个问题,就像在这里描述的其他人一样:

  • INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE只列出外键,主键和唯一约束。
  • 查找默认约束的唯一方法是在sys.default_constraints中查找它们。
  • 在这里还没有提到的是, 索引还会使列的删除失败,所以您还需要删除使用列的所有索引,然后才能继续删除列。

由此产生的脚本不是很漂亮,但我把它放在一个存储过程,以便能够重用它:

CREATE PROCEDURE DropColumnCascading @tablename nvarchar(500), @columnname nvarchar(500)
AS

SELECT CONSTRAINT_NAME, 'C' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, 'C'
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, 'I'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']'
            WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']'
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']'

print @sql
EXEC sp_executesql @sql

查找约束的名称或使用MSSQL设计视图并不总是一个选项。 我目前想制作一个脚本来删除一个有约束的列。 使用名称不是一个选项,因为名称是生成的,我想在不同的环境Dev / Sys / Prod /等使用脚本。 那么使用这个名字是不可能的,因为每个环境的名称会有所不同。 我可能不得不查看系统标签,但我同意应该有一个更容易的选项。


来自pvolders的回答正是我所需要的,但却错过了导致错误的统计数字。 这是相同的代码,减去创建一个存储过程,再加上枚举统计数据并将其删除。 这是最好的,我可以拿出来,所以如果有一个更好的方式来确定什么样的统计数据需要删除,请添加。

DECLARE @tablename nvarchar(500), 
        @columnname nvarchar(500)

SELECT  @tablename = 'tblProject',
        @columnname = 'CountyKey'


SELECT CONSTRAINT_NAME, 'C' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, 'C'
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, 'I'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

INSERT INTO #dependencies
SELECT s.NAME, 'S'
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc 
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c 
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID(@tableName)
AND c.NAME = @columnname
AND s.NAME LIKE '_dta_stat%'

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']'
            WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']'
            WHEN 'S' THEN 'DROP STATISTICS [' + @tablename + '].[' + @dep_name + ']'
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']'

print @sql
EXEC sp_executesql @sql

这是一个脚本,它将删除列及其默认约束。 适当地替换MYTABLENAMEMYCOLUMNNAME

declare @constraint_name sysname, @sql nvarchar(max)

select @constraint_name = name 
from sys.default_constraints 
where parent_object_id = object_id('MYTABLENAME')
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id('MYTABLENAME')
    and name = 'MYCOLUMNNAME'
    )

set @sql = N'alter table MYTABLENAME drop constraint ' + @constraint_name
exec sp_executesql @sql

alter table MYTABLENAME drop column MYCOLUMNNAME

go




sql-server-2005