SQLServer2008/2012 删除所有表视图存储过程_sqlserve2012如何删除存储过程-优快云博客

本文提供了一套完整的SQL Server 2008/2012数据库中批量删除所有表、视图及存储过程的脚本。通过使用游标和动态SQL的方式，首先解除表之间的外键约束，然后依次删除表、视图和存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLServer2008/2012 删除所有表视图存储过程

-------------------删除所有的表-------------------
use xuwenbin111
--/第1步**********删除所有表的外键约束*************************/
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
GO
--/第2步**********删除所有表*************************/
Go
declare @sql varchar(8000)
while (select count(*) from sysobjects where type='U')>0
begin
SELECT @sql='drop table ' + name
FROM sysobjects
WHERE (type = 'U')
ORDER BY 'drop table ' + name
exec(@sql)
end
-------------------删除所有的视图-------------------
use xuwenbin111
GO
declare @sql varchar(8000)
while (select count(*) from sysobjects where type='V')>0
begin
SELECT @sql='drop view ' + name
FROM sysobjects
WHERE (type = 'V')
ORDER BY 'drop view ' + name
exec(@sql)
end
-------------------删除所有的存储过程-------------------
use xuwenbin111
GO
declare @sql varchar(8000)
while (select count(*) from sysobjects where type='P')>0
begin
SELECT @sql='drop proc ' + name
FROM sysobjects
WHERE (type = 'P')
ORDER BY 'drop table ' + name
exec(@sql)
end