none
How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

    Question


  • How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?


    I tried using DROP Tables, Truncate Database, Delete and many more but it is not working.  
    I want to delete all tables using Query Analyzer, i.e. through SQL Query.

    Please help me out in this concern.

    Nishith Shah
    Wednesday, September 13, 2006 8:42 AM

Answers

  • hi Nishith Shah

    try this

    EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

    this is a hidden SP in sql server, this will be executed for each table in the database you connected (you cant rollback this)

    if u want to delete it from the command prompt try this

    EXEC xp_cmdshell 'SQLCMD -U <user> -P <password> -Q 'EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" ' ,no_output

    Best of luck.

    Gurpreet S. Gill

    Wednesday, September 13, 2006 9:16 AM
  • Hi Shimmy,

    I think you meant to delete all NON-system tables. BTW, as a precaution to your script you should have added

    if db_name() IN ('master', 'msdb', 'model', 'tempdbd')--may be few more
    
        return - 1
    

    I suggest to check this blog post 

    Delete all data in database (when you have FKs) 

    (to get the idea)

    and

    How to get information about all databases without a loop


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, March 28, 2011 1:15 PM
    Moderator
  • Try:

     

    declare @SQL nvarchar(max)
    
    SELECT @SQL = STUFF((SELECT ', ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) 
    
    FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE '%lku'
    FOR XML PATH('')),1,2,'')
    
    SET @SQL = 'DROP TABLE ' + @SQL
    
    PRINT @SQL
    
    --EXECUTE (@SQL) -- uncomment to actually delete the tables
    

    The solution comes from not so well known fact shared in Just Learned:

    http://beyondrelational.com/justlearned/posts/83/you-can-drop-multiple-tables-using-single-drop-statement.aspx

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Friday, December 09, 2011 8:22 PM
    Moderator

All replies

  • hi Nishith Shah

    try this

    EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

    this is a hidden SP in sql server, this will be executed for each table in the database you connected (you cant rollback this)

    if u want to delete it from the command prompt try this

    EXEC xp_cmdshell 'SQLCMD -U <user> -P <password> -Q 'EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" ' ,no_output

    Best of luck.

    Gurpreet S. Gill

    Wednesday, September 13, 2006 9:16 AM
  • Hi Gurpreet,
    it worked man.......... thanx a lot for your reply!

    Nishith Shah
    Wednesday, September 13, 2006 9:35 AM
  • Thanks man

     

     

    Wednesday, September 13, 2006 10:03 AM
  • Hi Gurpreet! once again.

    you have shown me the perfect way to delete/drop all table using single SQL statement.

    tell me if i just want to truncate/delete all the tables then how can i?

    pls reply

    Nishith
    Friday, September 15, 2006 10:46 AM
  • Hay man what you are asking for,if you just check my reply, the answere is there

    ok, try this, this will delete/truncate all the Data from each table for in the database you connected

    EXEC sp_MSforeachtable @command1 = "DELETE FROM ?"

    EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

    I too explain it now, as sp_MSforeachtable is Stored Procedure, that will execute for all the tables for database & @command1  is variable which will run against each table for connected database, now whatever you will write in the double quotes, that will be act as a command for each table, where '?' is the name of the table.

    try this, it will clear your comcepts

    EXEC sp_MSforeachtable @command1 = "SELECT * FROM ?"  -- Selects all the rows form all the table

    EXEC sp_MSforeachtable @command1 = "PRINT '?'" --Just print the tables names with owner(dbo)

    For more understanding, go for the MSDN or google, this is the right way.

    If still you are confused do call me any time(I am an Indian, 24x7) at +91-99495-60051

    Regards,

    Thanks.

    Gurpreet S. Gill

     

     

    Friday, September 15, 2006 11:15 AM
  • Hello Gurpreet,

    thanks a lot for helping man and giving your cell # also. It worked again...
    So, where r u working? as a?

    do contact me anyhow on me.poison@gmail.com or nishith82@hotmail.com
    atleast send me a blank email, i will understand its u.

    thanks,
    Nishith
    Tuesday, September 19, 2006 12:21 PM
  • You won't be able to run TRUNCATE against all tables if you have foreign keys references

    Here is one way to circumvent that

    -- First disable referential integrity
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    GO

    EXEC sp_MSForEachTable '
     IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
      DELETE FROM ?
     else
      TRUNCATE TABLE ?
    '
    GO

    -- Now enable referential integrity again
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO


    Denis the SQL Menace
    http://sqlservercode.blogspot.com/

     

    Tuesday, September 19, 2006 1:30 PM
    Moderator
  • Thanks Denis, ya these things need to consider, before applying delete/truncate command.

    Regards,

    Thanks.

    Gurpreet S. Gill

     

    Friday, September 22, 2006 5:21 AM
  • HI people

     I want to do this in MS Access database . Delete all tables . Is there a hidden SP here also ? or some other way . .

    Plz help

    Thursday, September 28, 2006 7:17 AM
  • hi

    i cant say anything about this, better to go for the MS-Access forum.

    or

    if you know the visual basic you can write the macro for that.

    just check this link

    http://www.codecomments.com/message725983.html

    Regards,

    thanks.

    Gurpreet S. Gill

     

     

     

    Thursday, September 28, 2006 8:04 AM
  • Code Snippet

    --Delete All Keys

    DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

    SET @Cursor = CURSOR FAST_FORWARD FOR

    SELECT DISTINCT sql = 'ALTER TABLE [' + 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

     

     

    Tuesday, July 15, 2008 6:42 PM
  • Sir,

    I saw your answer for deleting all the table from SQL database, I would like to ask Is it possible to delete all the user defined type from the database.

    Please .....
    Wednesday, November 25, 2009 6:04 AM
  • that's flawless
    Friday, January 15, 2010 3:36 AM
  • Use it.

    declare @tbl_name as varchar(256)
    declare @flag as int
    declare @name as varchar(256)
    set @flag=(select count(*) from sysobjects where type='U')
    while(@flag>0)
    begin
    set @name=(select top 1 name from sysobjects where type='U')
    set @tbl_name = 'drop table ' + @name
    exec(@tbl_name)
    set @flag=@flag-1
    end

    • Proposed as answer by EzBloke Wednesday, August 04, 2010 2:06 PM
    Tuesday, August 03, 2010 7:28 AM
  •  

    Hi friends,

     

    Try this procedure,which drops all the table which doesnt have reference with other table.

    exec

     

    sp_MSforeachtable "DROP TABLE ? P4RINT '? dropped' "

     

    by

    sathishsql

    Wednesday, December 29, 2010 5:10 AM
  • Hi Gill and thanks for your reply.

    I execute a Create script on my server and it executed on the master db, adding a zillion tables to it.

    I now need to delete all the tables that are system tables, how can this be done?


    Shimmy
    Monday, March 28, 2011 12:40 PM
  • Try

    exec sp_MSforeachtable
    @command1 = 'drop table ''? ''',
    @whereand = ' and xtype = ''U'''

    execute first with print to verify:
    exec sp_MSforeachtable
    @command1 = 'PRINT ''? ''',
    @whereand = ' and xtype = ''U'''

    Monday, March 28, 2011 1:08 PM
  • Hi Shimmy,

    I think you meant to delete all NON-system tables. BTW, as a precaution to your script you should have added

    if db_name() IN ('master', 'msdb', 'model', 'tempdbd')--may be few more
    
        return - 1
    

    I suggest to check this blog post 

    Delete all data in database (when you have FKs) 

    (to get the idea)

    and

    How to get information about all databases without a loop


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, March 28, 2011 1:15 PM
    Moderator
  • Hi, I use SQL Server 2008 R2 Express, to delete all table, I use feature in SQL,

    1. Right - Click on DB that's table you to delete
    2. Generate Scripts..
    3. Next
    4. Select specific database objects (select table you want to delete)
    5. Next
    6. Save scripts to a specific location, Save to new query window (opt)
    7. Click button advance
    8. on Options Script DROP and CREATE, choose Script DROP > OK
    9. Next
    10. The script appear on the Screen, you can just click Run

    nb: this is not limited to DROP table, with this wizard we can generate script for Create, Alter, DROP FK, etc

    Hope this help.

    Regards,

    Tuesday, November 08, 2011 9:23 AM
  • Hi Gurpreet

    IS there a way to change the ? in sp_msforeachtable

    for example if i want to delete all data where table_name like'%lku'

    Can you illustrate this with a query.

     

    Regards

    Harsimran


    Harsimranjeet Singh
    Friday, December 09, 2011 8:12 PM
  • Try:

     

    declare @SQL nvarchar(max)
    
    SELECT @SQL = STUFF((SELECT ', ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) 
    
    FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE '%lku'
    FOR XML PATH('')),1,2,'')
    
    SET @SQL = 'DROP TABLE ' + @SQL
    
    PRINT @SQL
    
    --EXECUTE (@SQL) -- uncomment to actually delete the tables
    

    The solution comes from not so well known fact shared in Just Learned:

    http://beyondrelational.com/justlearned/posts/83/you-can-drop-multiple-tables-using-single-drop-statement.aspx

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Friday, December 09, 2011 8:22 PM
    Moderator
  • Hi Naomi , thanks for the reply

    I am getting error  on line  - > FROM Information_schema.tables of incorrect syntax..

    IS there a way to change ? in sp_MSforeachtable to disable trigger/drop table/delete records for table_name like '%lku'......

     

    I not able to use @whereand argument in the same system sp i.e. sp_MSforeachtable 

    Can suggest and give a brief illustration for the same.

     

     

    Regards

    Harsimranjeet Singh 


    Harsimranjeet Singh
    Friday, December 09, 2011 10:13 PM
  • I think it's possible, but I think the solution I suggest is simpler. I fixed the typo. See also the blog I referenced in another reply.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, December 11, 2011 12:57 AM
    Moderator
  • Informative thread indeed. Wish I saw it early enough. Managed to come up with the code below before I saw this thread.

    declare @TTName Table (TableSchemaTableName varchar (500), [status] int default 0);

    with AvailableTables (TableSchemaTableName) as

    (select

    QUOTENAME(TABLE_SCHEMA) +

    '.' +

    QUOTENAME(TABLE_NAME)

    from

    INFORMATION_SCHEMA.TABLES)

    insert into @TTName (TableSchemaTableName)

    select * from AvailableTables

    declare @TableSchemaTableName varchar (500)

    declare @sqlstatement nvarchar (1000)

    while 1=1

    begin

    set @sqlstatement = 'DROP TABLE ' + @TableSchemaTableName

    exec sp_executeSQL @sqlstatement

    print 'Dropped Table : ' + @TableSchemaTableName

    update @TTName set [status] = 1 where TableSchemaTableName = @TableSchemaTableName

    if (select count([Status]) from @TTName where [Status] = 0) = 0 break

    end




    Monday, July 30, 2012 11:35 AM
  • Mpumelelo S,

    It appears that your script has an infinite WHILE loop.

    At any rate, instead of deleting all the tables, do this:

    1. Backup the database

    2. Script out all objects you want to save using SSMS Script Wizard

    3. Rename/drop the database

    4. Create a new database


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012




    Friday, September 28, 2012 11:34 PM
    Moderator
  • Gurpreet

    It works.. Thanks,

    Sunday, October 28, 2012 1:53 AM
  • Use below Sp to DELETE/DROP/TRUNCATE all tables even though tables have relations.

    CREATE PROCEDURE udpFlushAllTablesByDBName
    (
    @DBName  VARCHAR(200),
    @FlushType  VARCHAR(20)
    )
    AS
    BEGIN
    
    SET NOCOUNT ON;
    
    IF EXISTS (SELECT TOP 1 1 FROM SYS.TABLES WHERE OBJECT_ID = OBJECT_ID('tmpTable'))
    BEGIN
    DROP TABLE tmpTable
    END
    
    DECLARE @Query
    NVARCHAR(MAX);
    
    SET @Query = 
    
    'WITH EliminateUnwanted (Name,[Object_ID],parent_object_id ,referenced_object_id)  AS  
    (
    SELECT
    [T].Name,
    [T].[Object_ID],
    [F].parent_object_id,
    [F].referenced_object_id
    FROM ' + @DBName + '.SYS.TABLES [T]
    LEFT JOIN ' + @DBName + '.SYS.FOREIGN_KEYS [F] ON [T].object_id = [F].parent_object_id   
    WHERE [T].Name NOT IN (''SysDiagrams'', ''tmpTable'') AND [T].TYPE = ''U''  
    ),  
    SetPriority (Name,[Object_ID],parent_object_id ,referenced_object_id, parentObjectName, Priorities)  AS  
    (
    SELECT
    *,
    OBJECT_NAME(referenced_object_id) AS parentObjectName,
    CASE
    WHEN referenced_object_id IS NULL AND [Object_ID] NOT IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted )  THEN 1
    WHEN [Object_ID] NOT IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted )  THEN 2
    WHEN ([Object_ID] IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted ) AND parent_object_id IS NULL)  THEN 4
    ELSE 3    
    END ''PRIORITY''
    FROM EliminateUnwanted
    ),
    DuplicateRemoval (Occurence, Name,[Object_ID],parent_object_id ,referenced_object_id, parentObjectName, Priorities)  AS  
    (
    SELECT  
    ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS Occurence
    ,* 
    FROM SetPriority 
    )
    SELECT 
    ROW_NUMBER() OVER(ORDER BY Priorities) RowNo,
    Name,
    Object_ID,
    parent_object_id,
    referenced_object_id,
    parentObjectName,
    Priorities 
    INTO tmpTable
    FROM DuplicateRemoval 
    WHERE Occurence = 1'
    
    
    --SELECT @Query
    EXECUTE sp_executesql @Query
    
    DECLARE 
    @TableName  VARCHAR(100),
    @Count  BIGINT,
    @FlushMethod  VARCHAR(30);
    
    SELECT @FlushMethod =  CASE 
    WHEN @FlushType = 'TRUNCATE' THEN 'TRUNCATE TABLE ' 
    WHEN @FlushType = 'DROP' THEN 'DROP TABLE ' 
    ELSE 'DELETE ' 
    END;
    
    SET @Count = 1
    
    WHILE EXISTS(SELECT TOP 1 1 FROM tmpTable WHERE RowNo = @Count )
    BEGIN
    SELECT @TableName = NAME FROM tmpTable WHERE RowNo = @Count
    SET @Query = @FlushMethod + @DBName + '.dbo.' + @TableName
    
    EXECUTE sp_executesql @Query
    
    SET @Count = @Count + 1;
    END
    
    DROP TABLE tmpTable
    
    SET NOCOUNT OFF;
    
    END

    How to Use:

    EXEC udpFlushAllTablesByDBName 'TestDB', 'DROP'

    OR

    EXEC udpFlushAllTablesByDBName 'TestDB', 'DELETE'

    OR

    EXEC udpFlushAllTablesByDBName 'TestDB', 'TRUNCATE'

    Regards,

    Balaganesan Ravichandran


    Friday, January 04, 2013 12:25 PM
  • Works fine if you want to play with the mouse a bit, but from a command prompt, Gurpreet's way makes more sense.  You can just issue the command until it no longer fails to deal with foreign relations, or if you have tangled relations you will have to release the constraints before you can make it all happen.

    When you drop the database you use all your users and all their permissions as well.

    Tuesday, November 05, 2013 8:54 PM