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.

score 6 · Answer 1 · answered Apr 13 '11 at 20:18

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.

score 2 · Answer 2 · edited Jun 17 '11 at 14:46

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 '11 at 14:46

DarthJDG

16,511
11
49
56

answered Jun 17 '11 at 11:53

Rizwana

151
1
2

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. – djangofan Apr 16 '12 at 15:28
@djangofan What prevents you from putting these lines into a script? – Marcel Oct 15 '12 at 06:47
Just in case you ever need to drop the views too, here is how this works: http://stackoverflow.com/a/11689661/79485 – Marcel Feb 01 '13 at 13:51

TBohnen.jnr · Accepted Answer · 2011-04-16T06:17:02.843

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 '11 at 06:17

answered Apr 13 '11 at 20:18

TBohnen.jnr

5,117
1
19
26

Thanks for the answer . I posted my finished script after getting your help. – djangofan Apr 13 '11 at 21:22
1

-1 for being not schema correct (script does not consider table schema, only name) and, far worse, being SQL injection prone – Remus Rusanu Apr 13 '11 at 21:38
@Remus Rusanu Where would the sql injection be introduced? – TBohnen.jnr Apr 14 '11 at 07:33
Thanks again TBohnen.jnr . Your answer, while not exactly what I needed is what made the answer I posted below possible. – djangofan Apr 15 '11 at 20:54
@REmus: If he's not accepting user input, SQL Injection is not a relevant concern. Presumably anyone who would be able to execute this code already has permissions to do bad things to the database. – Brian Apr 15 '11 at 20:56
@Brian: I can create a table named `.]; execute somethign; [`. When the script above is run, the 'something' gets executed. At best it can cause the script to error. But I can also exploit this script to mount a privilege escalation attack: if I have the CREATE TABLE privilege, by creating a specially named table I will cause my code of choice to be run by whoever is running this script, thus escalating my privilege to the privileges of the login running the script. This script *is* the very definition of SQL Injection. – Remus Rusanu Apr 15 '11 at 21:18
@djangofan, it's a pleasure glad it works. I suggest taking the advice of @Remus Rusanu though, I doubt that this will be used but only use it if you are certain it's safe. – TBohnen.jnr Apr 15 '11 at 21:24
@Remus: That only works if users with permission to run this script have Create Table permission but not "`execute something`" permission. I am assuming that is not the case for the OP, though it is possible, though unlikely, that it is the case. Usually one doesn't let users without full trust drop arbitrary tables. – Brian Apr 15 '11 at 21:24
1

The fix is trivial, just use proper quotes, with `QUOTENAME(TABLE_SCHEMA)` and `QUOTENAME(TABLE_NAME)`. – Remus Rusanu Apr 15 '11 at 21:25
1

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. – Remus Rusanu Apr 15 '11 at 21:27

score 0 · Answer 4 · answered Apr 13 '11 at 21:20

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

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

4 Answers4