CODEWITHSUNDEEP
sqlvb.netvisual-studio
Troy Mitchel
Troy Mitchel

Reputation: 1810

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 ex As Exception
End Try

Upvotes: 0

Views: 1638

Answers (3)

Philippe Grondier
Philippe Grondier

Reputation: 11148

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

Upvotes: 1

gbn
gbn

Reputation: 432667

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

Upvotes: 1

GSerg
GSerg

Reputation: 78210

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

Upvotes: 1

Related Questions