如何删除非系统的表，存储过程，自定义函数,触发器
如何删除非系统的表，存储过程，自定义函数,触发器

------解决方案--------------------
DROP TABLE 表名
DROP PROCEDURE 存储过程名
DROP FUNCTION 函数名
DROP TRIGGER 触发器名
------解决方案--------------------
如果还有一些内在关联的话 要先把关联去掉
例如定义了什么constraint a 之类的
要先去掉constraint约束
然后再进行drop操作
------解决方案--------------------
--删用户表
declare @Sql varchar(4000)
select @SQL= 'Drop table '
select @SQL=@SQL + [name] + 'Drop table ' from sysobjects where xtype= 'u '
exec @SQL
--删procedure
declare @Sql varchar(4000)
select @SQL= 'Drop proc '
select @SQL=@SQL + [name] + 'Drop proc ' from sysobjects where xtype= 'P '
exec (@SQL)
--其它就自已改动下
---
C
D
F
P
PK
R
S
U
V



------解决方案--------------------
/******执行此操作时请慎重,确认无误后把事务去掉既可******/
BEGIN TRAN --事务 开始

select * from sysobjects where (type= 'U ' or type= 'P ' or type= 'FN ' or type= 'TR ') and category <> 2
declare @StrName nvarchar(200)
--删除 函数
declare cursorFN cursor for
select name from sysobjects where type= 'FN '
open cursorFN
while 1=1
begin
fetch next from cursorFN into @StrName
if @@fetch_status <> 0 break
exec( 'DROP FUNCTION '+@StrName)
end
close cursorFN
deallocate cursorFN

--删除 存储过程
declare cursorP2 cursor for
select name from sysobjects where type= 'P ' and category <> 2
open cursorP2
while 1=1
begin
fetch next from cursorP2 into @StrName
if @@fetch_status <> 0 break
exec( 'DROP PROCEDURE '+@StrName)
end
close cursorP2
deallocate cursorP2

--删除 表 (删时自动删除触发器)
--1删无约束无外键的表
declare cursorU1 cursor for
select a.name from sysobjects a where not exists(select 1 from sysobjects b where a.id=b.parent_obj) and a.type= 'U '
open cursorU1
while 1=1
begin
fetch next from cursorU1 into @StrName
if @@fetch_status <> 0 break
exec( 'DROP TABLE '+@StrName)
end
close cursorU1
deallocate cursorU1

--2删有约束或有外键的表
while exists(select 1 from sysconstraints)
begin
declare cursorU2 cursor for
select c.name from sysobjects c join (select distinct id from sysconstraints a
where not exists(select 1 from sysforeignkeys b where a.id=b.rkeyid)) d on c.id=d.id where c.type= 'U '
open cursorU2
while 1=1
begin
fetch next from cursorU2 into @StrName
if @@fetch_status <> 0 break
exec( 'DROP TABLE '+@StrName)
end
close cursorU2
deallocate cursorU2
end
select * from sysobjects where (type= 'U ' or type= 'P ' or type= 'FN ' or type= 'TR ') and category <> 2

ROLLBACK TRANSACTION --事务 回滚