数据库删除所有表语句怎么写_数据库删除语句怎么写_mysql 所有表中查询语句怎么写 - 腾讯云开发者社区

数据库删除所有表语句怎么写

在数据库中删除所有表的操作需要谨慎进行，因为这个操作会永久删除所有数据。以下是一些常见数据库的删除所有表的语句：

MySQL

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `table1`, `table2`, ..., `tableN`;
SET FOREIGN_KEY_CHECKS=1;

或者使用存储过程：

DELIMITER $$
CREATE PROCEDURE `drop_tables`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_database_name';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @drop_sql = CONCAT('DROP TABLE ', tableName);
        PREPARE stmt FROM @drop_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;
CALL drop_tables();

PostgreSQL

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema') LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

SQL Server

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

EXEC sp_executesql @SQL;

Oracle

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE table1 CASCADE CONSTRAINTS';
    EXECUTE IMMEDIATE 'DROP TABLE table2 CASCADE CONSTRAINTS';
    -- 继续添加其他表
END;
/