》修改日志:
2016.10.06 创建
》说明
下面分享一个无参存储过程,功能是:使用存储过程,删除某个(指定)数据库中的所有自建表,自建表所包含的约束、索引等对象也将被删除,但不删除系统表项。
版本:
MSSQL2008R2
》引言
开发过程中,会需要将之前建立的表删除,而一个一个的手动删除,对于懒人来说,是一种多么痛的领悟。。。
不足:
这个存储过程,只是实现了表及其关联对象的删除,但没有实现数据库管理维护工作,例如:删除这些object之后,将分散的数据页进行“聚拢”等,以便加快对操作的反应速度,而MSSQL数据库是否已经帮我们作了这些?怎么做的?是否默认执行?能否自行编出相应的脚本,确切地管理数据库?有待讨论与研究,也希望各路大神能不吝赐教!
》code
USE DB_Test--此处修改为:你的数据库名
GO
IF OBJECT_ID('dbo.usp_DropAllTablesAndFK','P') IS NOT NULL
--若存在,则删除
--dbo可以修改为:其他schema
DROP PROC dbo.usp_DropAllTablesAndFK;
GO
CREATE PROC dbo.usp_DropAllTablesAndFK--定义存储过程(无参)
AS
BEGIN
SET NOCOUNT ON;
----------------先从最近创建的FK开始解除FK约束-------------
DECLARE c0 CURSOR FOR
SELECT S.name,T.name,F.name --获得架构名、子表名、FK名
FROM sys.foreign_keys AS F
INNER JOIN sys.tables AS T
ON F.parent_object_id = T.object_id
INNER JOIN SYS.schemas AS S
ON T.schema_id = S.schema_id
ORDER BY F.create_date DESC;--按时间大小降序排列,即从最近的创建的FK开始解除
DECLARE @sch_name varchar(100)
,@childName varchar(100)
,@fk_Name varchar(100)
,@SQL VARCHAR(8000);
OPEN c0;--执行游标指令并缓存结果于游标区域(内存)中
FETCH NEXT FROM c0 INTO @sch_name,@childName,@fk_Name
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @SQL='ALTER TABLE '+@sch_name+'.'+@childName+' DROP CONSTRAINT '+@fk_Name;
EXEC(@SQL);
FETCH NEXT FROM c0 INTO @sch_name,@childName,@fk_Name;
END
CLOSE c0;--一定要关闭游标(实际上,是删除游标所占用的内存空间),之后若想再使用,必须重新打开
DEALLOCATE c0;--释放游标(其内存空间被释放)
PRINT 'Drop foreign keys success!';
-------------------------------------------------
-------------已解除FK,可以直接删除所有表-------------
DECLARE c1 CURSOR FOR--定义游标
SELECT S.name,T.name --获得用户定义的表的名称
FROM sys.tables as T
INNER JOIN sys.schemas as S
ON T.schema_id = S.schema_id
WHERE TYPE='U';
DECLARE @tbName varchar(100);
SET @SQL='DROP TABLE ';
OPEN c1;--执行游标指令并缓存结果于游标区域(内存)中
FETCH NEXT FROM c1 INTO @sch_name,@tbName--
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @SQL=@SQL +@sch_name+'.'+@tbName+',';
FETCH NEXT FROM c1 INTO @sch_name,@tbName;
END;
CLOSE c1;--一定要关闭游标(实际上,是删除游标所占用的内存空间),之后若想再使用,必须重新定义
DEALLOCATE c1;--释放游标(其内存空间被释放)
IF LEN(@SQL) > LEN('DROP TABLE ')
BEGIN
SET @SQL=LEFT(@SQL,LEN(@SQL)-1);--需要去掉最后一个的逗号
EXEC(@SQL);
PRINT 'Drop tables success!';
END
SET NOCOUNT OFF;
PRINT 'Procedure done!';
END
-------------------------------------------------
GO--MSSQL中记录下本存储过程