今闲来无事写些SQL,欢迎大家来拍砖
来源:互联网 发布:hold it against me 编辑:程序博客网 时间:2024/06/04 23:28
今闲来无事,胡乱搞点SQL
功能是删除用户创建的表、视图、存储过程
需要对哪个数据库操作,连接上直接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=''u'')
set @st=''drop table ''+@tablename
exec(@st)
print @tablename
set @it=@it+1
end'
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'delallviews') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
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=''v''
print @vcountrows
while @iv<@vcountrows
begin
select @viewname=name from sysobjects where name = (select top 1 name from sysobjects where type=''v'')
set @sv=''drop view ''+@viewname
exec(@sv)
print @viewname
set @iv=@iv+1
end'
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'delallprocedures') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
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=''p''
print @pcountrows
while @ip<@pcountrows
begin
select @procedurename=name from sysobjects where name = (select top 1 name from sysobjects where type=''p'')
set @sp=''drop procedure ''+@procedurename
exec(@sp)
print @procedurename
set @ip=@ip+1
end'
END
GO
--carry out
exec delalltables
exec delallviews
exec delallprocedures
print ' after deleting'
--check the result
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'
- 今闲来无事写些SQL,欢迎大家来拍砖
- 欢迎大家来拍砖
- 写了个MySQL数据备份小工具,放出来跟大家分享一下,欢迎拍砖。
- CANDENCE 焊盘封装心得(欢迎拍砖 都来拍砖)
- 谈谈内存池 (欢迎大家一起讨论,欢迎拍砖)
- 结构体数组做映射(写了个风格还算靠谱的程序, 欢迎大家找茬拍砖, 共同进步)
- DBlue 数据库访问组件(中间件) =欢迎大家拍砖=
- 学Linux的一些经验,和大家交流欢迎拍砖
- 配置有线和无线网络笔记linux欢迎大家拍砖
- PHP目录以及文件遍历类欢迎大家拍砖
- 分享一个MVC的多层架构,欢迎大家拍砖斧正
- 闲来无事,写篇日志
- 闲来无事,写篇日志
- 闲来无事,写点什么呢?
- 欢迎大家来做客!
- 欢迎大家来交流
- 欢迎大家来坐坐
- 欢迎大家来坐坐
- 开源面向对象数据库 db4o 之旅,第 2 部分: db4o 查询方式
- 开源面向对象数据库 db4o 之旅,第 3 部分: 深入db4o
- 安装fedora7碰到的问题和解决
- 使用Abator生成iBatis代码框架
- 在MFC中导入位图
- 今闲来无事写些SQL,欢迎大家来拍砖
- Java 的新 feeling
- 关于Struts2的一些实例代码
- 有用的国外开源项目网址-JAVA
- The whole procedure of build eCos
- 使.Net程序在未安装framework的电脑上运行(公布方法、源代码)
- C++--在单文档的应用程序增加多个视图
- 两个命令的用法(uic)以及生成makefile和创建VC程序
- 10分钟学会使用qmake