sql server 删除主键、外键、索引、约束的脚本

最近公司项目要升级新版本，涉及到数据库升级中各种约束。亦是整理出如下脚本方便以后查询。

--删除全文索引
DECLARE c0 cursor for
SELECT
'DROP FULLTEXT INDEX ON '+tab.name+';'
FROM
sys.fulltext_indexes idx
JOIN sys.tables tab
ON (idx.object_id = tab.object_id);
open c0
declare @c0 varchar(8000)
fetch next from c0 into @c0
while(@@fetch_status=0)
begin
exec(@c0)
fetch next from c0 into @c0
end
close c0
deallocate c0

Go

--删除约束和唯一约束
DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
from sysobjects
where xtype = 'D' OR xtype = 'UQ'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1

Go

--删除外键
DECLARE c2 cursor for
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
begin
exec(@c2)
fetch next from c2 into @c2
end
close c2
deallocate c2

Go

--删除主键
DECLARE c3 cursor for
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
from sysobjects
where xtype = 'PK'
open c3
declare @c3 varchar(8000)
fetch next from c3 into @c3
while(@@fetch_status=0)
begin
exec(@c3)
fetch next from c3 into @c3
end
close c3
deallocate c3

Go

 

--删除索引
DECLARE c4 cursor for
SELECT
'DROP INDEX '+ idx.name +' ON '+tab.name+';'
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_primary_key <> 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);
open c4
declare @c4 varchar(8000)
fetch next from c4 into @c4
while(@@fetch_status=0)
begin
exec(@c4)
fetch next from c4 into @c4
end
close c4
deallocate c4

Go