When working with legacy SQL Server 2008 databases, you might encounter situations where you need to purge numerous user tables while preserving the database structure and security settings. The traditional approach of dropping and recreating the database isn't viable when:

• You need to maintain existing permissions and roles
• The database contains complex security configurations
• You want to preserve non-table objects (views, procedures, etc.)

Here's a T-SQL script that generates and executes DROP TABLE statements for all user tables:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
DROP TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
AND s.name NOT IN ('sys', 'INFORMATION_SCHEMA');

PRINT @sql; -- Review before execution
-- EXEC sp_executesql @sql; -- Uncomment to actually execute

Before running any mass deletion:

• Backup your database: Always create a full backup
• Check dependencies: Foreign key constraints might cause errors
• Transaction safety: Wrap in a transaction for rollback capability

This extended script handles foreign key constraints:

-- Generate drop statements for all constraints first
DECLARE @dropConstraints NVARCHAR(MAX) = N'';
SELECT @dropConstraints += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + 
' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys fk
INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id;

-- Then generate table drop statements
DECLARE @dropTables NVARCHAR(MAX) = N'';
SELECT @dropTables += N'
DROP TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0;

-- Combine and execute
BEGIN TRY
    BEGIN TRANSACTION;
    EXEC sp_executesql @dropConstraints;
    EXEC sp_executesql @dropTables;
    COMMIT TRANSACTION;
    PRINT 'Tables and constraints dropped successfully';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH

For smaller batches or visual confirmation:

1. Open SSMS and connect to your database
2. Navigate to Tables in Object Explorer
3. Use Shift or Ctrl to multi-select tables
4. Right-click and select "Delete"
5. In the dialog, click "Show Dependencies" to verify
6. Check "Close existing connections" if needed
7. Click "OK" to execute

When deleting 100+ tables:

• Transaction log growth: Monitor your log file size
• System resource usage: Consider off-peak hours for large operations
• Locking impact: Single transaction vs. batched approaches

When administering SQL Server 2008 databases, you'll occasionally encounter situations requiring mass deletion of user tables while preserving database structure and security settings. Manually dropping 100+ tables through SSMS GUI is impractical and error-prone.

This script generates and executes DROP TABLE statements for all user tables:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + '];' + CHAR(13)
FROM sys.tables 
WHERE is_ms_shipped = 0 -- Exclude system tables
AND name NOT LIKE 'sys%';

PRINT @sql; -- Review before execution
-- EXEC sp_executesql @sql; -- Uncomment to execute

For targeted removal within specific schemas:

DECLARE @schemaName NVARCHAR(128) = 'YourSchema';
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'DROP TABLE [' + @schemaName + '].[' + name + '];' + CHAR(13)
FROM sys.tables 
WHERE schema_id = SCHEMA_ID(@schemaName);

EXEC sp_executesql @sql;

For production environments, wrap in a transaction:

BEGIN TRY
    BEGIN TRANSACTION;
    
    DECLARE @sql NVARCHAR(MAX) = '';
    SELECT @sql = @sql + 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + '];' + CHAR(13)
    FROM sys.tables 
    WHERE is_ms_shipped = 0;
    
    EXEC sp_executesql @sql;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH

Before deletion, export the table list for documentation:

SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName,
    create_date AS CreationDate
INTO #TablesToDrop
FROM sys.tables
WHERE is_ms_shipped = 0;

-- Export to CSV
EXEC xp_cmdshell 'bcp "SELECT * FROM #TablesToDrop" queryout "C:\temp\TablesToDrop.csv" -c -t, -T -S' + @@SERVERNAME;

For databases with FK constraints, use this modified approach:

-- Generate drop statements with constraint handling
SELECT 
    'ALTER TABLE [' + SCHEMA_NAME(fk.schema_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] 
    DROP CONSTRAINT [' + fk.name + '];' AS DropFK,
    'DROP TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '];' AS DropTable
FROM sys.tables t
LEFT JOIN sys.foreign_keys fk ON fk.referenced_object_id = t.object_id
WHERE t.is_ms_shipped = 0
ORDER BY DropFK DESC; -- Drop constraints first