locked
How to Drop all tables in database RRS feed

  • Question

  • How to Drop all tables in database?
    How to drop table according to the constraints ?
    Monday, June 29, 2009 9:00 AM

Answers

  • Just to provide you another alternative, you can combine the above approaches and improve the logic a little then you can basically generate 2 queries without using cursors and execute them diectly without copy pasting as follows:

    DECLARE @newline AS CHAR(2) = CHAR(13) + CHAR(10)
    DECLARE @fkSQL AS NVARCHAR(MAX) = ''
    DECLARE @tblSQL AS NVARCHAR(MAX) = ''
    
    SELECT @fkSQL = @fkSQL + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(name) + ' GO' + @newline
    FROM sys.foreign_keys
    
    SELECT @tblSQL = @tblSQL + 'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ' GO' + @newline
    FROM sys.tables
    
    EXEC (@fkSQL)
    EXEC (@tblSQL)



    Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)
    • Marked as answer by Adam Tappis Tuesday, June 30, 2009 1:41 PM
    Monday, June 29, 2009 2:19 PM

All replies

  • WHy you want to do that? or just trying few tricks?
    Mangal Pardeshi BI
    SQL With Mangal
    Monday, June 29, 2009 9:04 AM
  • -- declare @procName sysname
     
    declare someCursor cursor FOR
        SELECT name FROM sysobjects WHERE type = 'U' AND objectproperty(id, 'IsMSShipped') = 0
     
    open someCursor
    fetch next FROM someCursor INTO @procName
    while @@FETCH_STATUS = 0
    begin
        exec('drop table' + @procName)
        fetch next FROM someCursor INTO @procName
    end
     
    close someCursor
    deallocate someCursor
    go
    Monday, June 29, 2009 9:06 AM
  • Before dropping the tables we need to drop all the Foreign Constraints also.
    To drop all foreign keys in a DB use the following
    DECLARE @DropQuery varchar(255)
    
    -- Drop all the FKs
    DECLARE @curFKTblName sysname
    DECLARE @curFKName sysname
    
    DECLARE FK_Cursor CURSOR READ_ONLY
    FOR SELECT TABLE_NAME, CONSTRAINT_NAME
          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
         WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    
    OPEN FK_Cursor
    
    FETCH NEXT FROM FK_Cursor INTO @curFKTblName, @curFKName
    
    PRINT 'Begin dropping foreign keys...'
    
    WHILE (@@FETCH_STATUS = 0) 
    BEGIN
     
      SET @DropQuery = 'ALTER TABLE ' + @curFKTblName + ' DROP CONSTRAINT ' + @curFKName
      PRINT '  Removing Foreign Key: ' + CAST(@curFKName AS varchar)
      EXEC (@DropQuery)
      
      FETCH NEXT FROM FK_Cursor INTO @curFKTblName, @curFKName
    
    END
    
    PRINT 'Dropping foreign keys has been completed...'
    
    CLOSE FK_Cursor
    DEALLOCATE FK_Cursor
    

    • Proposed as answer by Brian Borg Wednesday, December 2, 2009 2:01 AM
    Monday, June 29, 2009 9:26 AM
  • First, be sure to have a backup ready or the script to recreate the db. Second, is not it easier to drop the database and create it again?


    AMB
    Monday, June 29, 2009 12:36 PM
  • Run this query. Take the results form Result pane.And run in the Query Analyzer.
    SELECT

     

    'DROP TABLE '+[Name]
    FROM SYS.OBJECTS WHERE TYPE='U'

    Thanks
    Ramesh.M

    Monday, June 29, 2009 2:02 PM
  • Just to provide you another alternative, you can combine the above approaches and improve the logic a little then you can basically generate 2 queries without using cursors and execute them diectly without copy pasting as follows:

    DECLARE @newline AS CHAR(2) = CHAR(13) + CHAR(10)
    DECLARE @fkSQL AS NVARCHAR(MAX) = ''
    DECLARE @tblSQL AS NVARCHAR(MAX) = ''
    
    SELECT @fkSQL = @fkSQL + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(name) + ' GO' + @newline
    FROM sys.foreign_keys
    
    SELECT @tblSQL = @tblSQL + 'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ' GO' + @newline
    FROM sys.tables
    
    EXEC (@fkSQL)
    EXEC (@tblSQL)



    Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)
    • Marked as answer by Adam Tappis Tuesday, June 30, 2009 1:41 PM
    Monday, June 29, 2009 2:19 PM