一次性删除数据库所有表存储过程和函数_2019版 清空Zoomla!逐浪CMS全库.sql

--第一步：删除所有约束、表、视图等SQL脚本
--删除所有外键约束
DECLARE @SQL VARCHAR(99)
DECLARE CUR_CONSTRAINT CURSOR LOCAL FOR
SELECT
'ALTER TABLE '+
CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
+OBJECT_NAME(parent_object_id)+' DROP CONSTRAINT '+OBJECT_NAME(object_id)
FROM sys.objects AS O JOIN sys.schemas AS S on O.schema_id=S.schema_id
WHERE O.type IN('C','D','F')
OPEN CUR_CONSTRAINT
FETCH CUR_CONSTRAINT INTO @SQL
WHILE @@FETCH_STATUS =0
BEGIN
EXEC(@SQL)
FETCH CUR_CONSTRAINT INTO @SQL
END
CLOSE CUR_CONSTRAINT
DEALLOCATE CUR_CONSTRAINT

--第二步：删除所有视图（存储过程、函数等用同样的方法）
DECLARE @SQL VARCHAR(99)
DECLARE CUR_VIEW CURSOR LOCAL FOR
SELECT
'IF OBJECT_ID('''+
CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
+OBJECT_NAME(object_id)
+''') IS NOT NULL'+
+' DROP VIEW '+
CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
+OBJECT_NAME(object_id)
FROM sys.objects AS O JOIN sys.schemas AS S on O.schema_id=S.schema_id
WHERE O.type IN('V')
OPEN CUR_VIEW
FETCH CUR_VIEW INTO @SQL
WHILE @@FETCH_STATUS =0
BEGIN
EXEC(@SQL)
FETCH CUR_VIEW INTO @SQL
END
CLOSE CUR_VIEW
DEALLOCATE CUR_VIEW

--第三步： 删除所有表
DECLARE @SQL VARCHAR(99)
DECLARE CUR_TABLE CURSOR LOCAL FOR
SELECT
'DROP TABLE '+
CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
+O.name
FROM sys.objects AS O JOIN sys.schemas AS S on O.schema_id=S.schema_id
WHERE O.type='U'
OPEN CUR_TABLE
FETCH CUR_TABLE INTO @SQL
WHILE @@FETCH_STATUS =0
BEGIN
EXEC(@SQL)
FETCH CUR_TABLE INTO @SQL
END
CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE

--第四步：删除函数及其它异质
DROP FUNCTION [dbo].[ZL_StringSplitTable] /*161212增加删除-将1,2,3,4这样的字符串,在SQL中转化为表格,用于进行 IN查询函数*/
DROP FUNCTION [dbo].[ZL_StripeHtmlTag] /*170112增加删除-内容关键词函数*/
DROP FUNCTION [dbo].[ZL_StripeTrimstr] /*180424增加删除-HTML超文本HTML转纯文本过滤器函数*/
drop table [dbohg].[Schema]
drop table [dbohg].[set]