CODEWITHSUNDEEP
sqlsql-server
overwhelmedaf
overwhelmedaf

Reputation: 63

Executing multiple drop table statements in 1 sql file

I am trying to drop 200 tables from my database and save the sql statements to 1 .sql file so they can be run in one go on each copy of the database.

I would like to use a separate drop table statement for each table so I can easily reference what line any errors appear on.

I have tried running a query in the following format

DROP TABLE schema.tablename GO
DROP TABLE schema.tablename GO
DROP TABLE schema.tablename GO

When I execute the query, I get 

Incorrect syntax near 'GO'.

For each line.

What is the proper way of doing this that will still allow me to easily locate errors on the tables that are unable to be dropped?

Upvotes: 1

Views: 1118

Answers (2)

D Stanley
D Stanley

Reputation: 152566

Note in the documentation that:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

So if you are trying to execute this as a single "command" then go will not work - you'll need to execute it as separate commands.

If you are using a batch command processor, then also note:

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

So try putting the GO commands on their own lines:

DROP TABLE schema.tablename 
GO
DROP TABLE schema.tablename 
GO
DROP TABLE schema.tablename 
GO

Upvotes: 1

HereGoes
HereGoes

Reputation: 1320

The GO has to be on a separate line, or use a semi-colon

DROP TABLE schema.tablename 
GO
DROP TABLE schema.tablename;

Upvotes: 2

Related Questions