제약 조건이있는 SQL Server 2005 드롭 열

제약 조건이있는 SQL Server 2005 드롭 열

---

"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에 그런 것이 없습니까?

---

이 쿼리는 주어진 테이블에 대한 기본 제약 조건을 찾습니다. 예쁘지 않습니다. 동의합니다.

select 
    col.name, 
    col.column_id, 
    col.default_object_id, 
    OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, 
    dobj.name as def_name
from sys.columns col 
    left outer join sys.objects dobj 
        on dobj.object_id = col.default_object_id and dobj.type = 'D' 
where col.object_id = object_id(N'dbo.test') 
and dobj.name is not null

[편집] Julien N의 댓글에 따라 업데이트 됨

---

다음은 기본 제약 조건과 함께 열을 삭제하는 스크립트입니다. 교체 MYTABLENAME및 MYCOLUMNNAME적절.

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

---

아마도 조금 더 도움이 될 것입니다.

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 변수 만 설정하면됩니다.

---

> 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 개의 조인으로 요청을 실행해야합니다.

---

또한 계단식 드롭을 사용할 수 없다는 것이 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

---

information_schema 시스템 뷰를 쿼리하여 제약 조건 이름을 가져올 수 있습니다.

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

---

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

---

Just to build on Jeremy Stein's answer, I created a stored procedure for this, and set it up so it can be used to delete a column that has or does not have default constraints. It's not real efficient since it's querying sys.columns twice, but it works.

CREATE PROCEDURE [dbo].[RemoveColumnWithDefaultConstraints] 
    -- Add the parameters for the stored procedure here
    @tableName nvarchar(max), 
    @columnName nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @ConstraintName nvarchar(200)
    SELECT @ConstraintName = Name 
    FROM SYS.DEFAULT_CONSTRAINTS 
    WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
        AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = (@columnName) 
        AND object_id = OBJECT_ID(@tableName))
    IF @ConstraintName IS NOT NULL
        EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @ConstraintName)

    IF EXISTS(SELECT * FROM sys.columns WHERE Name = @columnName  
        AND Object_ID = Object_ID(@tableName))
        EXEC('ALTER TABLE ' + @tableName + ' DROP COLUMN ' + @columnName)       
END
GO

---

Looking up the name of the contraint or using MSSQL design view is not always an option. I currently want to make a script for deleting a column with a contraint on it. Using the name is not an option since the name is generated and I want to use the script in different environments Dev/Sys/Prod/etc. Using the name is then not possible because the contraint names will differ per environment. I will probably have to look into the systems tabel but I agree there should be an easier option available.

---

I believe explicitly dropping the constraints prior to dropping the column is a "cleaner" solution. This way, you don't drop constraints you may not be aware of. If the drop still fails, you know there are additional constraints remaining. I like being in control of exactly what is happening to my database.

Plus, scripting the drops explicitly guarantees the script and hopefully the results to be repeatable in exactly the way you intend.

---

What do you mean randomly generated? You can look up the constraints on the specific column in management studio or via the sys.tables view and find what the name(s) are.

Then, you can change your script to drop the constraints prior to dropping the column. What type of constraint is this? If it is a foreign key constraint, make sure that doing this won't hurt the data integrity within you database.

---

I just ran into this. You can delete the column with constraints using MSSQL design view. Right click on the column you want to be dropped (with or without constraints) and you are able to delete this without any problems. Ha.. I looked stupid already.

---

Just Generate Scripts for the table. There you can find the name of all constraints.

참고URL : https://stackoverflow.com/questions/314998/sql-server-2005-drop-column-with-constraints