none
SQL: Delete all but one table RRS feed

  • Question

  • private void tableclean() 
    {
        using (SqlConnection sqcon
        = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Peter\Documents\main.mdf;Integrated Security=True;Connect Timeout=30"))
        {
            using (SqlCommand cmd = new SqlCommand("DROP TABLE * WHERE TABLE_NAME != def;", sqcon))
            {
                sqcon.Open();
                cmd.ExecuteReader();
                sqcon.Close();
                DropDownList1.DataBind();
            }
        }
    }

    DROP TABLE * WHERE TABLE_NAME != def;

    // def is the name of the table i need to keep. This seems like it is a simple query but does not work. I'm not sure if TABLE_NAME is even proper syntax. I'M sure its a simple solution but I cant find it anywhere online. 

    Thursday, September 26, 2013 11:56 PM

Answers

  • Hi petersmcintyre,

    There is no purely sql statement which can drop mutiplate tables.

    One way to do this, as far as I know, we can use the procedure like below:

    declare @sql varchar(max)
    
    declare @tablenames varchar(max)
    
    select @tablenames = coalesce(@tablenames + ', ','') + Table_Name from INFORMATION_SCHEMA.TABLES    
    
    where Table_Name != 'def' 
    
    --Disable disable foreign key checks sql server
    
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";
    
    set @sql = 'drop table ' + @tablenames
    
    -- Able disable foreign key checks sql server
    
    EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
    
    exec (@sql)
    

    Thanks &Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by petersmcintyre Friday, September 27, 2013 8:57 PM
    Friday, September 27, 2013 9:15 AM
    Moderator

All replies

  • Hi,. The query syntax shouldn't have a where clause (see here for more).

    DROP TABLE mTableName;

    Friday, September 27, 2013 12:06 AM
  • Hi ,

    Thanks for your posting.

    From your description, I think that this issue is related to ADO.NET ,Data Platform Development forums  is more suitable for it,So I move it to Data Platform Development forums  .

    Data Platform Development forums  :http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Hope these help.


    Lilia Gong <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    Friday, September 27, 2013 1:27 AM
  • Hi petersmcintyre,

    There is no purely sql statement which can drop mutiplate tables.

    One way to do this, as far as I know, we can use the procedure like below:

    declare @sql varchar(max)
    
    declare @tablenames varchar(max)
    
    select @tablenames = coalesce(@tablenames + ', ','') + Table_Name from INFORMATION_SCHEMA.TABLES    
    
    where Table_Name != 'def' 
    
    --Disable disable foreign key checks sql server
    
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";
    
    set @sql = 'drop table ' + @tablenames
    
    -- Able disable foreign key checks sql server
    
    EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
    
    exec (@sql)
    

    Thanks &Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by petersmcintyre Friday, September 27, 2013 8:57 PM
    Friday, September 27, 2013 9:15 AM
    Moderator
  • I think this solution can help you.

    http://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string


    Please Mark as Answer and Vote as Helpful if I helped.

    Also please visit my blog http://www.msguy.me/



    Friday, September 27, 2013 12:13 PM