分类导航
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLOceanBase

sql – 如何从数据库中删除所有内容(表,视图,存储过程,函数)？

MsSQL   发布时间：2022-05-16  发布网站：大佬教程  code.js-code.com
大佬教程收集整理的这篇文章主要介绍了sql – 如何从数据库中删除所有内容(表,视图,存储过程,函数)？，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
我想删除数据库,表,视图,存储过程和函数中的所有内容.我找到了一些链接,但它们不适用于复杂的数据库.它没有删除表.这是我在本网站上发现的脚本的自己版本并进行了调整. 
/* Drop all non-system stored procs */ 
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254)
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.procedures WHERE [name] = @Name)

WHILE @name is not null 
BEGIN 
    SELECT @sql = 'DROP PROCEDURE [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +']' 
    EXEC (@sql) 
    PRINT 'Dropped Procedure: ' + @name 

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
    SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.procedures WHERE [name] = @Name) 
END 
GO 

/* Drop all views */ 
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254)
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.views WHERE [name] = @Name)

WHILE @name IS NOT NULL 
BEGIN 
    SELECT @sql = 'DROP VIEW [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +']' 
    EXEC (@sql) 
    PRINT 'Dropped View: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) 
    SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.views WHERE [name] = @Name)
END 
GO 

/* Drop all Foreign Key consTraints */ 
DECLARE @name VARCHAR(128) 
DECLARE @consTraint VARCHAR(254) 
DECLARE @sql VARCHAR(254) 
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' ORDER BY TABLE_Name) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 CONSTraiNT_scheR_957_11845@A FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' ORDER BY TABLE_Name)

WHILE @name is not null 
BEGIN 
    SELECT @consTraint = (SELECT TOP 1 constraint_name FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' AND TABLE_NAME = @name AND CONSTraiNT_scheR_957_11845@A = @scheR_957_11845@a ORDER BY constraint_name) 
    WHILE @consTraint IS NOT NULL 
    BEGIN 
        SELECT @sql = 'alter table [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +'] DROP CONSTraiNT [' + RTRIM(@consTraint) +']' 
        --BEGIN TRY        
            EXEC (@sql)
            PRINT 'Dropped FK ConsTraint: ' + @consTraint + ' on ' + @name
        --END TRY
        --BEGIN CATCH
        --  PRINT (@sql)
        --END CATCH
        SELECT @consTraint = (SELECT TOP 1 constraint_name FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' AND constraint_name <> @constraint aND TABLE_NAME = @name AND CONSTraiNT_scheR_957_11845@A = @scheR_957_11845@a ORDER BY constraint_name) 
    END 
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' ORDER BY TABLE_Name) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 CONSTraiNT_scheR_957_11845@A FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' ORDER BY TABLE_Name)
END 
GO 

/* Drop all PriMary Key consTraints */ 
DECLARE @name VARCHAR(128) 
DECLARE @consTraint VARCHAR(254) 
DECLARE @sql VARCHAR(254) 
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' ORDER BY TABLE_Name) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 CONSTraiNT_scheR_957_11845@A FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' ORDER BY TABLE_Name)

WHILE @name IS NOT NULL 
BEGIN 
    SELECT @consTraint = (SELECT TOP 1 constraint_name FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' AND TABLE_NAME = @name AND CONSTraiNT_scheR_957_11845@A = @scheR_957_11845@a ORDER BY constraint_name) 
    WHILE @consTraint is not null 
    BEGIN 
        SELECT @sql = 'alter table [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +'] DROP CONSTraiNT [' + RTRIM(@consTraint)+']' 
        IF @sql LIKE '%PK_arrival_detail%' BEGIN PRINT @sql END
        EXEC (@sql) 
        PRINT 'Dropped PK ConsTraint: ' + @consTraint + ' on ' + @name 
        SELECT @consTraint = (SELECT TOP 1 constraint_name FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' AND constraint_name <> @constraint aND TABLE_NAME = @name ORDER BY constraint_name) 
    END 
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' ORDER BY TABLE_Name) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 CONSTraiNT_scheR_957_11845@A FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' ORDER BY TABLE_Name)
END 
GO 

/* Drop all tables */ 
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254) 
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.tables WHERE [name] = @Name)

WHILE @name IS NOT NULL 
BEGIN 
    SELECT @sql = 'drop table [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +']' 
    EXEC (@sql) 
    PRINT 'Dropped Table: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) 
    SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.tables WHERE [name] = @Name)
END 
GO 

/* Drop all functions */ 
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254) 
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN',N'IF',N'TF',N'FS',N'FT') AND category = 0 ORDER BY [name]) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.objects WHERE [name] = @Name)

WHILE @name IS NOT NULL 
BEGIN 
    SELECT @sql = 'DROP FUNCTION [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +']' 
    EXEC (@sql) 
    PRINT 'Dropped Function: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN',N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) 
    SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.objects WHERE [name] = @Name)
END 
GO

问题仅出在丢弃约束和丢弃表上.对于与另一个表有关系的表,它总是失败.如何修复我的脚本以使其适用于更复杂的数据库？

解决方法

您提供的脚本几乎已完成.我已经在这里和那里调整了一些条件,以使其在复杂情况下更加稳健.此版本支持删除具有相同名称但不同架构的2个对象.我还添加了一个删除数据库触发器的部分,因为它可能会阻止您在某些情况下删除表.

我用AdventureWorks Databases和我自己的数据库测试了这个(有点非常复杂.)

/* Drop all non-system stored procs */ 
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254)
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.procedures WHERE [name] = @Name)

WHILE @name is not null 
BEGIN 
    SELECT @sql = 'DROP PROCEDURE [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +']' 
    --PRINT (@sql)
    EXEC (@sql) 
    PRINT 'Dropped Procedure: ' + @name 

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
    SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.procedures WHERE [name] = @Name) 
END 
GO 

/* Drop all database triggers */
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254)

SELECT @name = (SELECT TOP 1 T.name FROM sys.triggers AS T WHERE T.parent_id = 0 /* database trigger */ ORDER BY [name])

WHILE @name is not null 
BEGIN 
    SELECT @sql = 'DROP trigGER [' + RTRIM(@Name) +'] ON DATABASE' 
    EXEC (@sql) 
    PRINT 'Dropped trigGER: ' + @name 

    SELECT @name = (SELECT TOP 1 T.name FROM sys.triggers AS T WHERE T.parent_id = 0 /* database trigger */ AND [name] > @name ORDER BY [name])
END 
GO 

/* Drop all views */ 
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254)
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.views WHERE [name] = @Name)

WHILE @name IS NOT NULL 
BEGIN 
    SELECT @sql = 'DROP VIEW [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +']' 
    EXEC (@sql) 
    PRINT 'Dropped View: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) 
    SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.views WHERE [name] = @Name)
END 
GO 

/* Drop all Foreign Key consTraints */ 
DECLARE @name VARCHAR(128) 
DECLARE @consTraint VARCHAR(254) 
DECLARE @sql VARCHAR(254) 
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' ORDER BY TABLE_Name) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 CONSTraiNT_scheR_957_11845@A FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' ORDER BY TABLE_Name)

WHILE @name is not null 
BEGIN 
    SELECT @consTraint = (SELECT TOP 1 constraint_name FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' AND TABLE_NAME = @name AND CONSTraiNT_scheR_957_11845@A = @scheR_957_11845@a ORDER BY constraint_name) 
    WHILE @consTraint IS NOT NULL 
    BEGIN 
        SELECT @sql = 'alter table [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +'] DROP CONSTraiNT [' + RTRIM(@consTraint) +']' 
        BEGIN TRY
            EXEC (@sql)
            PRINT 'Dropped FK ConsTraint: ' + @consTraint + ' on ' + @name
        END TRY
        BEGIN CATCH
            PRINT (@sql)
        END CATCH
        SELECT @consTraint = (SELECT TOP 1 constraint_name FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' AND constraint_name <> @constraint aND TABLE_NAME = @name AND CONSTraiNT_scheR_957_11845@A = @scheR_957_11845@a ORDER BY constraint_name) 
    END 
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' ORDER BY TABLE_Name) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 CONSTraiNT_scheR_957_11845@A FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'FOREIGN KEY' ORDER BY TABLE_Name)
END 
GO 

/* Drop all PriMary Key consTraints */ 
DECLARE @name VARCHAR(128) 
DECLARE @consTraint VARCHAR(254) 
DECLARE @sql VARCHAR(254) 
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' ORDER BY TABLE_Name) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 CONSTraiNT_scheR_957_11845@A FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' ORDER BY TABLE_Name)

WHILE @name IS NOT NULL 
BEGIN 
    SELECT @consTraint = (SELECT TOP 1 constraint_name FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' AND TABLE_NAME = @name AND CONSTraiNT_scheR_957_11845@A = @scheR_957_11845@a ORDER BY constraint_name) 
    WHILE @consTraint is not null 
    BEGIN 
        SELECT @sql = 'alter table [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +'] DROP CONSTraiNT [' + RTRIM(@consTraint)+']' 
        --PRINT @sql
        EXEC (@sql)
        PRINT 'Dropped PK ConsTraint: ' + @consTraint + ' on ' + @name 
        SELECT @consTraint = (SELECT TOP 1 constraint_name FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' AND constraint_name <> @constraint aND TABLE_NAME = @name AND CONSTraiNT_scheR_957_11845@A = @scheR_957_11845@a ORDER BY constraint_name) 
    END 
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' ORDER BY TABLE_Name) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 CONSTraiNT_scheR_957_11845@A FROM @R_302_4036@ION_scheR_957_11845@A.TABLE_CONSTraiNTS WHERE consTraint_catalog=db_name() AND constraint_type = 'PRIMary KEY' ORDER BY TABLE_Name)
END 
GO 

/* Drop all tables */ 
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254) 
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) 
SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.tables WHERE [name] = @Name)

WHILE @name IS NOT NULL 
BEGIN 
    SELECT @sql = 'drop table [' + @scheR_957_11845@a + '].[' + RTRIM(@Name) +']' 
    EXEC (@sql) 
    PRINT 'Dropped Table: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) 
    SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.tables WHERE [name] = @Name)
END 
GO 

/* Drop all functions */ 
DECLARE @name VARCHAR(128) 
DECLARE @sql VARCHAR(254) 
DECLARE @scheR_957_11845@a VARCHAR(128)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN',N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) 
    SELECT @scheR_957_11845@a = (SELECT TOP 1 Schema_name(scheR_957_11845@a_id) FROM sys.objects WHERE [name] = @Name)
END 
GO

大佬总结

以上是大佬教程为你收集整理的sql – 如何从数据库中删除所有内容(表,视图,存储过程,函数)？全部内容，希望文章能够帮你解决sql – 如何从数据库中删除所有内容(表,视图,存储过程,函数)？所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
标签：表视图
猜你在找的MsSQL相关文章
其他相关热搜词更多
phpJavaPython程序员load中如何string使用参数jquery开发安装listlinuxiosandroid工具javascriptcap