Solved

drop multiple tables with one command

Posted on 2011-09-02
6
307 Views
Last Modified: 2012-05-12
maybe
drop table1,table2,table3

I want to drop 50 tables

is there a right click shortcut
0
Comment
Question by:rgb192
6 Comments
 
LVL 14

Accepted Solution

by:
leoahmad earned 100 total points
ID: 36474217
select 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'

from INFORMATION_SCHEMA.TABLES

where TABLE_NAME like 'YOUR_CRITERIA%'

 

run the output from that query to drop the tables.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 36474264
I confirm that you cannot have 1 drop statement to drop several tables at once.
the script above could help to generate all the drop statements...
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 100 total points
ID: 36474350
is it difficult to write 50 lines, copy paste, maybe excel can help
first column : drop table
second column : your table name
third column: ;

then copy down 1 & 3 column to row 50
fill 50 table name (or copy paste from some other place)
select all copy ansdd paste into a notepad...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 36474691
with that many i'd recommend doing it in a script and double/treble checking...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 36478158
You should be able to do:
DROP TABLE table1, table2, table3

>>is there a right click shortcut<<
From Object Explorer Details in SSMS you can select them all and then delete them all at once.
0
 

Author Closing Comment

by:rgb192
ID: 36478196
thanks
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now