DB의 전체 PK, FK 찾기 및 삭제

-- PK 명칭과 해당 테이블을 select 해온다.
select t.name, p.name from sysobjects p, sysobjects t
where  p.xtype = 'PK' and t.id = p.parent_obj and t.xtype = 'U'
order by 1
go

--FK 명칭과 해당 테이블을 select 해온다.
select t.name, f.name  
from sysobjects f, sysobjects t 
where f.xtype = 'F' and f.parent_obj = t.id
go


-- dbo 소유의 모든 FK 를 찾아서 삭제해줌.
DECLARE fk_con CURSOR
FOR 
    select t.name, f.name  from sysobjects f, sysobjects t 
    where f.xtype = 'F' and f.parent_obj = t.id

OPEN fk_con
DECLARE @tname sysname, @kname sysname
FETCH NEXT FROM fk_con INTO @tname, @kname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 print @tname + @kname
 EXEC ('Alter table dbo.'+@tname+ ' DROP constraint ' + @kname)
 FETCH NEXT FROM fk_con INTO @tname, @kname
END
PRINT 'All dbo FK constraints have been dropped from the database. '

DEALLOCATE fk_con
go


-- dbo 소유의 모든 PK 를 찾아서 삭제해줌.
DECLARE pk_con CURSOR
FOR 
    select t.name, p.name from sysobjects p, sysobjects t
    where  p.xtype = 'PK' 
    and t.id = p.parent_obj 
    and t.xtype = 'U' 
    and t.name not in ('dtproperties')

OPEN pk_con
DECLARE @tname sysname, @kname sysname
FETCH NEXT FROM pk_con INTO @tname, @kname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 print @tname + @kname
    EXEC ('Alter table dbo.'+@tname+ ' DROP constraint ' + @kname)
 FETCH NEXT FROM pk_con INTO @tname, @kname
END
PRINT 'All dbo PK constraints have been dropped from the database. '

DEALLOCATE pk_con
go

출처 : http://dbmaster.tistory.com/36