情况:在远程数据库删除表执行太慢,表过多,数据库无权删除
结果:保留空数据库
方法:利用sql语句,查询网络文摘解决.
说明:
有些有约束，不能直接delete，需要先删除所有约束,语句:
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
select ‘truncate table ‘ + Name + ‘;‘ from sysobjects where xtype=‘U‘ order by name asc;
该条语句执行之后会将数据库中所有的表都查询出来，复制出来之后执行truncate语句即可
删除数据库所有表,语句:
select ‘truncate table ‘ + Name + ‘;‘ from sysobjects where xtype=‘U‘ order by name asc; declare @tname varchar(8000) set @tname=‘‘ select @tname=@tname + Name + ‘,‘ from sysobjects where xtype=‘U‘ select @tname=‘drop table ‘ + left(@tname,len(@tname)-1) exec(@tname)
如果需要删除存储过程等只需要将上面的做如下修改就行了的where xtype=‘U‘ 改成 where xtype=‘P‘，drop table 改成 drop Procedure
删除数据库所有存储过程,语句:
select ‘truncate Procedure ‘ + Name + ‘;‘ from sysobjects where xtype=‘P‘ order by name asc; declare @tname varchar(8000) set @tname=‘‘ select @tname=@tname + Name + ‘,‘ from sysobjects where xtype=‘P‘ select @tname=‘drop Procedure ‘ + left(@tname,len(@tname)-1) exec(@tname)