How to drop multiple tables like %...% in sql server

Question

I am trying to delete particular tables routinely from the sys.tables in vb.net. here is code, but it does not work.

Try
    Dim MyCommand As New Odbc.OdbcCommand("", MyConnection)
    MyCommand.CommandText = "drop from sys.tables where tabname like '% " & span & "%'"
    Dim result As Integer = MyCommand.ExecuteNonQuery
    MyCommand.Dispose()
Catch ex As Exception
End Try

GSerg · Accepted Answer · 2011-05-27 17:12:31Z

1

Please never try to change sysviews data. They are for your information. You change that data by executing DDL commands.

In your case you need one DROP TABLE tablename per table.
You can use sys.tables to get a list of tables to execute a DROP against, but the DROPs must be separate commands.

Also, it's good to accustom yourself to the correct Dispose pattern:

Using MyCommand As New Odbc.OdbcCommand("drop table foo;", MyConnection)
    MyCommand.ExecuteNonQuery()  
End Using

answered May 27, 2011 at 17:12

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Troy Mitchel Over a year ago

@GSerf @gbn @Philippe Ok I'll change my code around to get table names then loop through them and drop one by one. thanks.

gbn · Accepted Answer · 2011-05-27 17:12:38Z

1

You have to loop and issue single DROP TABLEs explicitly.

You could batch this up into a single call with may DROP TABLE calls.

Edit:

There is also dbo.xp_execresultset that could generate it for you. I don't know if it's still in SQL Server 2005 either to be honest. This is undocumented so YMMV

answered May 27, 2011 at 17:12

gbn

435k84 gold badges602 silver badges690 bronze badges

Comments

Philippe Grondier · Accepted Answer · 2011-05-27 17:13:03Z

1

You should browse sys.tables and find all tables corresponding to your search. You will then, for each one of them, send the following instruction:

MyCommand.commandText = "Drop Table '" & tableName & "'"

answered May 27, 2011 at 17:13

Philippe Grondier

11.1k3 gold badges37 silver badges75 bronze badges

Collectives™ on Stack Overflow

How to drop multiple tables like %...% in sql server

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related