Drop all tables in a SQL Server database from Batch file?

Question

I have this batch file that outputs a list of all the tables to a file. I am looking for the command that will input this list and generate a whole list of drop statements to drop all the tables with?

:: droptables.bat
set SQLVER=100
if NOT EXIST "%PROGRAMFILES%\Microsoft SQL Server\100\Tools\BINN\osql.exe" (
  @echo MS SQL Server 2008 not found.
  set SQLVER=90
  if NOT EXIST "%PROGRAMFILES%\Microsoft SQL Server\90\Tools\BINN\osql.exe" (
    @echo MS SQL Server 2005 not found.
    set SQLVER=80
    if NOT EXIST "%PROGRAMFILES%\Microsoft SQL Server\80\Tools\BINN\osql.exe" (
      @echo MS SQL Server is not yet installed.
      pause
      exit
    )
  )
)
@echo Your SQL Server version is %SQLVER% (100=2008,90=2005, and 80=2000)    
if exist "%PROGRAMFILES%\Microsoft SQL Server\%SQLVER%\Tools\BINN\osql.exe" (
  "%PROGRAMFILES%\Microsoft SQL Server\%SQLVER%\Tools\BINN\osql" -E 
  -d "%PROJECT%PD_FSDB_ECitation" -h-1 -Q "select name from sysobjects where 
  type='U' order by name;" -o tableList.txt

The above query needs to be changed to create a list of drop statements instead of just table names. The tableList.sql file is just a simple list of drop table statements.

After generating the queryList.sql, then I want to run it like so:

osql -E -h-1 -i C:\MyFolder\queryList.txt

I know there is a way to generate a list of SQL statements from a SQL statement but I don't remember how to do it.

JNK · Accepted Answer · 2011-04-13 20:18:35Z

6

Why don't use just use the system stored proc sp_msforeachtable?

Run the following from your osql and you can bypass a lot of the extra work you are doing:

USE <databasename>
exec sp_msforeachtable 'DROP TABLE ?'

This proc basically builds a cursor and executes the query inside the single quotes once for each table, replacing ? with the schema-qualified table name, like dbo.table.

answered Apr 13, 2011 at 20:18

JNK

65.5k15 gold badges127 silver badges138 bronze badges

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

Comments

DarthJDG · Accepted Answer · 2011-06-17 14:46:12Z

2

Run the following from your SQL. It is simple and fast to delete tables from your DB:

USE <databasename>
exec sp_msforeachtable 'DROP TABLE ?'

edited Jun 17, 2011 at 14:46

DarthJDG

16.6k11 gold badges52 silver badges56 bronze badges

answered Jun 17, 2011 at 11:53

Rizwana

1511 silver badge2 bronze badges

3 Comments

djangofan Over a year ago

It is faster than writing a script yes, but once the script is created, there is nothing faster than just double clicking a script on your desktop.

Marcel Over a year ago

@djangofan What prevents you from putting these lines into a script?

Marcel Over a year ago

Just in case you ever need to drop the views too, here is how this works: stackoverflow.com/a/11689661/79485

TBohnen.jnr · Accepted Answer · 2011-04-16 06:17:02Z

1

I don't know if there is a command to do it but you can change your select statement so that it creates the drop statement for you:

select '--DROP TABLE ' + name + CHAR(10) + 'DROP TABLE ' + name + ' GO' from sysobjects where type='U'

EDIT: After comment with regards to schema not specified:

SELECT '--DROP TABLE ' + TABLE_NAME + CHAR(10) + 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  
FROM INFORMATION_SCHEMA.TABLES

EDIT: As Remus suggested in the comments, added quotename to make it less vulnerable to sql injection

edited Apr 16, 2011 at 6:17

answered Apr 13, 2011 at 20:18

TBohnen.jnr

5,1191 gold badge22 silver badges26 bronze badges

10 Comments

djangofan Over a year ago

Thanks for the answer . I posted my finished script after getting your help.

Remus Rusanu Over a year ago

-1 for being not schema correct (script does not consider table schema, only name) and, far worse, being SQL injection prone

TBohnen.jnr Over a year ago

@Remus Rusanu Where would the sql injection be introduced?

Remus Rusanu Over a year ago

The fix is trivial, just use proper quotes, with QUOTENAME(TABLE_SCHEMA) and QUOTENAME(TABLE_NAME).

Remus Rusanu Over a year ago

And as a generic rule: 'I don think someone will exploit this' is never an excuse to leave known defects in the code. Fix it, and then you'll know no one will ever exploit it.

|

djangofan · Accepted Answer · 2011-04-13 21:20:09Z

Here is how I did it:

set SQLVER=100
if NOT EXIST "%PROGRAMFILES%\Microsoft SQL Server\100\Tools\BINN\osql.exe" (
  @echo MS SQL Server 2008 not found.
  set SQLVER=90
  if NOT EXIST "%PROGRAMFILES%\Microsoft SQL Server\90\Tools\BINN\osql.exe" (
    @echo MS SQL Server 2005 not found.
    set SQLVER=80
    if NOT EXIST "%PROGRAMFILES%\Microsoft SQL Server\80\Tools\BINN\osql.exe" (
      @echo MS SQL Server is not yet installed.
      pause
      exit
    )
  )
)
@echo Your SQL Server version is %SQLVER% (100=2008,90=2005, and 80=2000)

if exist "%PROGRAMFILES%\Microsoft SQL Server\%SQLVER%\Tools\BINN\osql.exe" (
  "%PROGRAMFILES%\Microsoft SQL Server\%SQLVER%\Tools\BINN\osql" -E -h-1 
 -d MYDB -Q "select 'DROP TABLE ' + CAST(name AS 
 VARCHAR(30)) + ';' from sysobjects where type='U';" -o queries.sql
  :: remove sql result count line
  type queries.sql | findstr /V rows > queryList.sql  
  :: rename Identity table in the sql script because it uses a keyword
  type queryList.sql | findstr /V /C:"TABLE Identity" > runQueries.sql
  echo DROP TABLE [Identity]; >> runQueries.sql
  "%PROGRAMFILES%\Microsoft SQL Server\%SQLVER%\Tools\BINN\osql" -E -h-1 
 -d MYDB -i runQueries.sql -o dropResults1.txt
)
pause
:: Cleanup
del queryList.sql
del dropResults1.txt
del dropResults2.txt
del runQueries.sql
del queries.sql
exit

Collectives™ on Stack Overflow

Drop all tables in a SQL Server database from Batch file?

4 Answers 4

Comments

3 Comments

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related