sql-serversql-server-2008ssmssp-msforeachtable

How to drop all tables in a SQL Server database?


I'm trying to write a script that will completely empty a SQL Server database. This is what I have so far:

USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'

When I run it in the Management Studio, I get:

Command(s) completed successfully.

but when I refresh the table list, they are all still there. What am I doing wrong?


Solution

  • It doesn't work for me either when there are multiple foreign key tables.
    I found that code that works and does everything you try (delete all tables from your database):

    DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
    
    SET @Cursor = CURSOR FAST_FORWARD FOR
    SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
    
    OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    Exec sp_executesql @Sql
    FETCH NEXT FROM @Cursor INTO @Sql
    END
    
    CLOSE @Cursor DEALLOCATE @Cursor
    GO
    
    EXEC sp_MSforeachtable 'DROP TABLE ?'
    GO
    

    You can find the post here. It is the post by Groker.