DROP TABLE

Dear All,
Can I use DROP TABLE statement to drop more than one table at once. If
not, how can I drop (delete) so many tables from the data base at the same
time with the condition that I know a constant part on its name.
Best Regards
--
*********
IT Manager
DeLaval Ltd.
Cairo-Egypt
*********
|--|
|Islam is peace not Terror|
|--|Ibrahim
create table #t1 (col int)
create table #t2 (col int)
create table #t3 (col int)
DROP TABLE #t1,#t2,#t3
"Ibrahim Awwad" < ibrahim_awwad(at)hotmail(dot)com(antispa
m)> wrote in
message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
> Dear All,
> Can I use DROP TABLE statement to drop more than one table at once. If
> not, how can I drop (delete) so many tables from the data base at the same
> time with the condition that I know a constant part on its name.
> Best Regards
> --
> *********
> IT Manager
> DeLaval Ltd.
> Cairo-Egypt
> *********
> |--|
> |Islam is peace not Terror|
> |--||||Dear Uri,
First thanks for your reply, but let me give you an idea about the
problem I have. Our DB is having about thousand tables and some of them
replicated automatically each year. So the tables take the pattern (SC010001
)
so for example this file become (SC010101) for year 2001 and (SC010201) for
year 2002 ...etc. So all what I need for example is to delete all '01'
tables as example one time without riting theiere name one by one. I hope
that you got my idea.
Best Regards
"Uri Dimant" wrote:

> Ibrahim
> create table #t1 (col int)
> create table #t2 (col int)
> create table #t3 (col int)
> DROP TABLE #t1,#t2,#t3
>
>
> "Ibrahim Awwad" < ibrahim_awwad(at)hotmail(dot)com(antispa
m)> wrote in
> message news:B0BB5831-AC8D-4184-A5F8-4E7E73CB9EB7@.microsoft.com...
>
>|||Ibrahim
Copy-Paste the output in the QA and press F5
USE NorthWind
SELECT 'DROP TABLE '+
QUOTENAME(TABLE_SCHEMA) +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
'%Customers%'
"Ibrahim Awwad" < ibrahim_awwad(at)hotmail(dot)com(antispa
m)> wrote in
message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> Dear Uri,
> First thanks for your reply, but let me give you an idea about the
> problem I have. Our DB is having about thousand tables and some of them
> replicated automatically each year. So the tables take the pattern
(SC010001)
> so for example this file become (SC010101) for year 2001 and (SC010201)
for
> year 2002 ...etc. So all what I need for example is to delete all
'01'[vbcol=seagreen]
> tables as example one time without riting theiere name one by one. I hope
> that you got my idea.
> Best Regards
> "Uri Dimant" wrote:
>
If[vbcol=seagreen]
same[vbcol=seagreen]|||Hi Again Uri,
I tried what you did, it gave me the result DROP TABLE [dbo][Custome
rs]
.. What was this for... Can you tell me more in depth -if you can-...
"Uri Dimant" wrote:

> Ibrahim
> Copy-Paste the output in the QA and press F5
> USE NorthWind
> SELECT 'DROP TABLE '+
> QUOTENAME(TABLE_SCHEMA) +
> QUOTENAME(TABLE_NAME)
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> '.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> '%Customers%'
>
>
> "Ibrahim Awwad" < ibrahim_awwad(at)hotmail(dot)com(antispa
m)> wrote in
> message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> (SC010001)
> for
> '01'
> If
> same
>
>|||Ibrahim
I tested it on Nortwind database.You have to modify it for your needs.
Modify a LIKE operator in the WHERE condition to the actual table you want
to remove.
"Ibrahim Awwad" < ibrahim_awwad(at)hotmail(dot)com(antispa
m)> wrote in
message news:E2FFB248-D667-4D0D-A4BE-56E5C38B1C3C@.microsoft.com...[vbcol=seagreen]
> Hi Again Uri,
> I tried what you did, it gave me the result DROP TABLE [dbo][Cus
tomers]
> .. What was this for... Can you tell me more in depth -if you can-...
>
> "Uri Dimant" wrote:
>
them[vbcol=seagreen]
(SC010201)[vbcol=seagreen]
hope[vbcol=seagreen]
in[vbcol=seagreen]
once.[vbcol=seagreen]
the[vbcol=seagreen]|||Hi Again,
I think I got your idea, that to get a statement saying Drop Table ******
with all table names and execute them as a punch from the QA?
Am I right..
"Uri Dimant" wrote:

> Ibrahim
> Copy-Paste the output in the QA and press F5
> USE NorthWind
> SELECT 'DROP TABLE '+
> QUOTENAME(TABLE_SCHEMA) +
> QUOTENAME(TABLE_NAME)
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> '.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0 AND QUOTENAME(TABLE_NAME) LIKE
> '%Customers%'
>
>
> "Ibrahim Awwad" < ibrahim_awwad(at)hotmail(dot)com(antispa
m)> wrote in
> message news:E52B7B9F-0B25-4503-A625-96FEC3526E2E@.microsoft.com...
> (SC010001)
> for
> '01'
> If
> same
>
>|||Correct
"Ibrahim Awwad" < ibrahim_awwad(at)hotmail(dot)com(antispa
m)> wrote in
message news:27F96530-6006-4E08-8E65-A08E816D5050@.microsoft.com...
> Hi Again,
> I think I got your idea, that to get a statement saying Drop Table
******[vbcol=seagreen]
> with all table names and execute them as a punch from the QA?
> Am I right..
>
> "Uri Dimant" wrote:
>
them[vbcol=seagreen]
(SC010201)[vbcol=seagreen]
hope[vbcol=seagreen]
in[vbcol=seagreen]
once.[vbcol=seagreen]
the[vbcol=seagreen]