需求背景: 需要重建数据库表和视图对象时,或者需要从一个sqlserver库复制到另一个库时,希望先清空目标库的对象,可以使用下面的sql。
--删除所有约束
DECLARE c1 cursor for
select'alter table ['+ object_name(parent_obj)+'] drop constraint ['+name+']; '
from sysobjects
where xtype ='F'
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
--删除数据库所有表
declare @tname varchar(8000)
set @tname=''
select @tname=@tname+Name+',' from sysobjects where xtype='U' and name like 'View_%'
select @tname='drop table '+ left(@tname,len(@tname)-1)
exec(@tname)
--删除数据库所有视图
declare @vname varchar(8000)
set @vname=''
select @vname=@vname+Name+',' from sysobjects where xtype='V'
select @vname='drop View '+ left(@vname,len(@vname)-1)
exec(@vname)
我们的工作室的 Java ERP 作品,多多支持,指点,点击打开链接