SQL Server Performance

Deleting tables

Discussion in 'SQL Server 2005 General DBA Questions' started by california6, Sep 26, 2007.

  1. california6 New Member

    I have a database with over 200 tables. I have to delete like 170 tables and was wondering, what would be the best way to do it? Dont want to go table by table and drop. Is there a best way to do this?
    Thanks,
  2. ndinakar Member

    If you have a list of those table names, put it into an excel sheet, write up a formula for a concatenated string.
    'DROP TABLE ' + A1
    then copy the formula across all rows. You will have a big list of DROP TABLE statements. paste the entire column into query analyzer and hit F5. Then go get some coffee.
  3. FrankKalis Moderator

    That formula will yield an error in Excel.
    You have to add a = at the beginning, or a + which will also tell Excel to interpret the following string as a formula. [:)]
  4. ndinakar Member

    <P mce_keep="true">[quote user="FrankKalis"] <P>That formula will yield an error in Excel.</P><P>You have to add a = at the beginning, or a + which will also tell Excel to interpret the following string as a formula. <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"></P><DIV>[/quote]</DIV><DIV>Hey get off my back... [:p]</DIV><DIV>When you&nbsp;click the "fx" button, Excel will insert an "=" in the cell, then you choose the formula ( CONCATENATE). </DIV>
  5. FrankKalis Moderator

    Hey for such a simple formula you don't need the formula editor, do you? [:)]
  6. Madhivanan Moderator

    or
    Code:
    declare @tables varchar(8000)
    select @tables=''
    select @tables=@tables+','+table_name from information_schema.tables 
    where table_name not in (list of wanted tables)
    select @tables='Drop table '+substring(@tables,2,len(@tables))
    
    EXEC(@tables)
    
  7. FrankKalis Moderator

    Wouldn't you want to add the filter AND TABLE_TYPE = 'BASE TABLE' to avoid any errors when executing the statement? [;)]
  8. Madhivanan Moderator

    <P>Yes it is. I corrected it [:)]&nbsp; <P>[quote user="FrankKalis"] <P>Wouldn't you want to&nbsp;add the filter&nbsp;AND TABLE_TYPE = 'BASE TABLE' to avoid any errors when executing the statement? <IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"></P>[/quote]
  9. Madhivanan Moderator

    or
    declare @tables varchar(8000)
    select @tables=''
    select @tables=@tables+','+table_name from information_schema.tables
    where table_name not in (list of wanted tables) AND TABLE_TYPE = 'BASE TABLE'
    select @tables='Drop table '+substring(@tables,2,len(@tables))

    EXEC(@tables)

Share This Page