Wednesday, March 21, 2012

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|
> |--||||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'
> 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...
> > > 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|
> > > |--|
> >
> >
> >|||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...
> > 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...
> > > > 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
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...
> 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...
> > > 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...
> > > > > 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|
> > > > > |--|
> > > >
> > > >
> > > >
> >
> >
> >|||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...
> > 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...
> > > > 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|
> > > > |--|
> > >
> > >
> > >
>
>|||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
******
> 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...
> > > 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...
> > > > > 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|
> > > > > |--|
> > > >
> > > >
> > > >
> >
> >
> >sql

No comments:

Post a Comment