删除用户创建的表、视图、存储过程

功能是删除用户创建的表、视图、存储过程
需要对哪个数据库操作，连接上直接F5运行，测试前注意备份哦   或建个测试库
大家有啥好意见，欢迎讨论~~~
------------------------------------------------------------------------
--delete   all   the   tables、views、procedures   of   the   database
/******   Object:     three   procedures         Script   Date:   08/25/2007   17:17:28   by   Sunbird69(**)   ******/
print   '                 before   deleting '
select   name   as   tablename,type,crdate   from   sysobjects   where   type= 'u '
select   name   as   viewname,type,crdate   from   sysobjects   where   type= 'v '
select   name   as   procedurename,type,crdate   from   sysobjects   where   type= 'p '

SET   ANSI_NULLS   ON
GO
SET   QUOTED_IDENTIFIER   OFF
GO
--delete   all   user   tables
IF   NOT   EXISTS   (SELECT   *   FROM   sys.objects   WHERE   object_id   =   OBJECT_ID(N 'delalltables ')   AND   type   in   (N 'P ',   N 'PC '))
BEGIN
EXEC   dbo.sp_executesql   @statement   =   N '
create   procedure   delalltables
as
declare   @st   varchar(200),@tablename   nvarchar(30),@it   int,@tcountrows   int
set   @it=0
select   @tcountrows=count(name)   from   sysobjects   where   type= ' 'u ' '
print   @tcountrows
while   @it <@tcountrows
begin
select   @tablename=name   from   sysobjects   where   name   =   (select   top   1   name   from   sysobjects   where   type= &apos; &apos;u &apos; &apos;)
set   @st= &apos; &apos;drop   table   &apos; &apos;+@tablename
exec(@st)
print   @tablename
set   @it=@it+1
end &apos;
END
GO

SET   ANSI_NULLS   ON
GO
SET   QUOTED_IDENTIFIER   OFF
GO
--drop   all   views   tables
IF   NOT   EXISTS   (SELECT   *   FROM   sys.objects   WHERE   object_id   =   OBJECT_ID(N &apos;delallviews &apos;)   AND   type   in   (N &apos;P &apos;,   N &apos;PC &apos;))
BEGIN
EXEC   dbo.sp_executesql   @statement   =   N &apos;
create   procedure   delallviews
as
declare   @sv   varchar(200),@viewname   nvarchar(30),@iv   int,@vcountrows   int
set   @iv=0
select   @vcountrows=count(name)   from   sysobjects   where   type= &apos; &apos;v &apos; &apos;
print   @vcountrows
while   @iv <@vcountrows
begin
select   @viewname=name   from   sysobjects   where   name   =   (select   top   1   name   from   sysobjects   where   type= &apos; &apos;v &apos; &apos;)
set   @sv= &apos; &apos;drop   view   &apos; &apos;+@viewname
exec(@sv)
print   @viewname
set   @iv=@iv+1
end &apos;
END
GO

SET   ANSI_NULLS   ON
GO
SET   QUOTED_IDENTIFIER   OFF
GO
--drop   all   procedures
IF   NOT   EXISTS   (SELECT   *   FROM   sys.objects   WHERE   object_id   =   OBJECT_ID(N &apos;delallprocedures &apos;)   AND   type   in   (N &apos;P &apos;,   N &apos;PC &apos;))
BEGIN
EXEC   dbo.sp_executesql   @statement   =   N &apos;
create   procedure   delallprocedures
as
declare   @sp   varchar(200),@procedurename   nvarchar(30),@ip   int,@pcountrows   int
set   @ip=0
select   @pcountrows=count(name)   from   sysobjects   where   type= &apos; &apos;p &apos; &apos;
print   @pcountrows
while   @ip <@pcountrows
begin
select   @procedurename=name   from   sysobjects   where   name   =   (select   top   1   name   from   sysobjects   where   type= &apos; &apos;p &apos; &apos;)
set   @sp= &apos; &apos;drop   procedure   &apos; &apos;+@procedurename
exec(@sp)
print   @procedurename
set   @ip=@ip+1
end &apos;
END
GO

--carry   out
exec   delalltables
exec   delallviews
exec   delallprocedures

print   &apos;                 after   deleting &apos;
--check   the   result
select   name   as   tablename,type,crdate   from   sysobjects   where   type= &apos;u &apos;
select   name   as   viewname,type,crdate   from   sysobjects   where   type= &apos;v &apos;
select   name   as   procedurename,type,crdate   from   sysobjects   where   type= &apos;p &apos;
每次只删一个，不够狠的，

要删一起删，狠一点，破坏数据库是我的强项

declare   @tmp   varchar(8000)
set   @tmp   =   &apos; &apos;
select   @tmp   =   @tmp   +   &apos;drop   table   [ &apos;   +   name   +   &apos;]; &apos;   from   sysobjects   where   type   =   &apos;u &apos;
exec   @tmp

--还不够狠，加这个：

dump   transaction   dbname   with   no_log