drop all database objects
/*
Use this sql to drop all objects in a database.
*/
-- Drop all SP
declare @dropSp varchar(max)=''
select @dropSp=@dropSp+'DROP PROCEDURE '+QUOTENAME(SCHEMA_NAME(obj.schema_id))+'.'+ QUOTENAME(RTRIM(s.name)) +';'+CHAR(13)
FROM sysobjects s
left join sys.objects obj
on s.id = obj.object_id and s.name=obj.name
WHERE s.[type] = 'P' AND category = 0 ORDER BY s.[name]
--EXEC (@SQL)
Print @dropSp
go
/* Drop all views */
declare @dropViews varchar(max) =''
select @dropViews=@dropViews+'DROP VIEW '+QUOTENAME(SCHEMA_NAME(obj.schema_id))+'.'+ QUOTENAME(RTRIM(s.name)) +';'+CHAR(13)
FROM sysobjects s
left join sys.objects obj
on s.id = obj.object_id and s.name=obj.name
WHERE s.[type] = 'V' AND category = 0 ORDER BY s.[name]
--EXEC (@dropViews)
Print @dropViews
go
/* Drop all functions */
declare @dropFN varchar(max) =''
select @dropFN=@dropFN+'DROP FUNCTION '+QUOTENAME(SCHEMA_NAME(obj.schema_id))+'.'+ QUOTENAME(RTRIM(s.name)) +';'+CHAR(13)
FROM sysobjects s
left join sys.objects obj
on s.id = obj.object_id and s.name=obj.name
WHERE s.[type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY s.[name]
--EXEC (@dropFN)
Print @dropFN
go
/* Drop all Foreign Key constraints */
DECLARE @SQL VARCHAR(MAX)=''
SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(DB_NAME())+'.'+ QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'
--EXEC (@SQL)
PRINT @SQL
GO
--Drop all primary key
DECLARE @SQL VARCHAR(MAX)=''
SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(DB_NAME())+'.'+ QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
--EXEC (@SQL)
PRINT @SQL
GO
/* Drop all tables */
DECLARE @SQL VARCHAR(MAX)=''
select @SQL = @SQL+'drop TABLE '+QUOTENAME(DB_NAME())+'.'+QUOTENAME(SCHEMA_NAME(obj.schema_id))+'.'+ QUOTENAME(RTRIM(s.name)) +';'+CHAR(13)
FROM sysobjects s
left join sys.objects obj
on s.id = obj.object_id and s.name=obj.name
WHERE s.[type] = 'u' AND category = 0 ORDER BY s.[name]
--EXEC (@dropViews)
Print @SQL
go
最新文章
- [Tool]Inno Setup创建软件安装程序。
- Xamarin.Android经验之谈
- 如何编译ReactNative示例程序Examples
- Scrum项目4.0
- 前端编码规范（2）—— HTML 规范
- ubuntu笔记1
- makefile懒人版(单个文件编译)
- c#中反射
- 19_Python元组总结
- composer操作简单解析
- 20190326-HTML5标签、CSS的引用
- 前端学习之HTML
- js根据ip自动获取地址（省市区）
- day 9 - 2 函数练习
- 好好的P2P，咋说爆就爆？
- 2012年蓝桥杯省赛A组c++第2题(暴力求解古堡算式)
- js监听页面放大缩小
- Jquery选择器之父节点的子节点
- Linux上实现Windows的SQLPlus保存SQL历史记录功能
- 用python实现一个简单的socket网络聊天通讯 （Linux --py2.7平台与windows--py3.6平台）