MSSQL_删除所有自建表_存储过程

》修改日志：

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中记录下本存储过程