首页 > 代码库 > sql server快速删除整个数据库表和存储过程

sql server快速删除整个数据库表和存储过程

2024-07-11 17:07:21   218人阅读

情况:在远程数据库删除表执行太慢,表过多,数据库无权删除

结果:保留空数据库

方法:利用sql语句,查询网络文摘解决.

说明:

有些有约束，不能直接delete，需要先删除所有约束,语句:

DECLARE c1 cursor for    select ‘alter table [‘+ object_name(parent_obj) + ‘] drop constraint [‘+name+‘]; ‘    from sysobjects    where xtype = ‘F‘open c1declare @c1 varchar(8000)fetch next from c1 into @c1while(@@fetch_status=0)    begin        exec(@c1)        fetch next from c1 into @c1    endclose c1deallocate 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)

 


联系
我们