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

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开发者_C百科 ex As Exception
End Try

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

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

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 & "'"