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(antispam)> 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(antispam)> 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(antispam)> 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][Customers]
... 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(antispam)> 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(antispam)> 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][Customers]
> .. 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(antispam)> 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(antispam)> 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]