如何动态执行自动生成的SQL数据库表删除语句？

Got it, let's solve this problem so you don't have to manually copy those DROP statements anymore. Here are a couple of solid approaches to dynamically execute the generated drop commands for all your tables in SQL Server:

Approach 1: Clean Concise Method (SQL Server 2017+) #

If you're running SQL Server 2017 or later, you can use STRING_AGG to build a single script with all your DROP TABLE statements, then execute it in one go:

DECLARE @FullDropScript NVARCHAR(MAX);

-- Build the complete drop script with line breaks for readability
SELECT @FullDropScript = STRING_AGG(
    'DROP TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.NAME + '];',
    CHAR(13) + CHAR(10)
)
FROM sys.tables t;

-- Execute the generated script
EXEC sp_executesql @FullDropScript;

Approach 2: Handle Foreign Key Constraints (All SQL Server Versions) #

If your tables have foreign key relationships, the first approach will fail (you can't drop a parent table before its dependent child tables). This workaround temporarily disables foreign key checks, drops all tables, then re-enables the constraints:

-- Disable all foreign key constraints temporarily
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";

-- Drop every table in the database
EXEC sp_MSforeachtable "DROP TABLE ?";

-- Re-enable foreign key constraints
EXEC sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL";

Note: sp_MSforeachtable is an undocumented system stored procedure, but it's widely used and reliable for batch operations across all tables. If you prefer a fully documented method, you'd need to build a script that drops tables in child-to-parent order, but that adds more complexity.

内容的提问来源于stack exchange，提问作者user9393635