Deleting group of tables by drop table ?

Deleting group of tables by drop table ?

Post by D. Hoszowsk » Mon, 21 Jan 2002 01:54:13



Hi All,
I have problem to delete a group of tables with drop table (table_name).
I have made temporary user tables and now i want to delete that tables. Each
of them looks like:
DOK_(user_name)
for example:
DOK_Gua
DOK_Zua
DOK_Mal, etc.
I have tried to make a stored procedure like this one:
DECLARE my_cursor CURSOR FOR
select name from sysobjects where name LIKE 'DOK[_]%'
OPEN my_cursor
FETCH NEXT FROM my_cursor


BEGIN

    FETCH NEXT FROM my_cursor
END

... but it didn't work :(( It seems that drop table have to have direct
table name.

Any ideas how to do it ? or what i have missed in that script ?

Thanks a lot for any support
Wiht best regards
Dariusz Hoszowski

 
 
 

Deleting group of tables by drop table ?

Post by lindawi » Mon, 21 Jan 2002 02:00:53


D.,

You need to use dynamic sql:


Linda


> Hi All,
> I have problem to delete a group of tables with drop table (table_name).
> I have made temporary user tables and now i want to delete that tables.
Each
> of them looks like:
> DOK_(user_name)
> for example:
> DOK_Gua
> DOK_Zua
> DOK_Mal, etc.
> I have tried to make a stored procedure like this one:
> DECLARE my_cursor CURSOR FOR
> select name from sysobjects where name LIKE 'DOK[_]%'
> OPEN my_cursor
> FETCH NEXT FROM my_cursor


> BEGIN

>     FETCH NEXT FROM my_cursor
> END

> ... but it didn't work :(( It seems that drop table have to have direct
> table name.

> Any ideas how to do it ? or what i have missed in that script ?

> Thanks a lot for any support
> Wiht best regards
> Dariusz Hoszowski


 
 
 

Deleting group of tables by drop table ?

Post by D. Hoszowsk » Mon, 21 Jan 2002 02:12:44


siure - thanks lindawie


> D.,

> You need to use dynamic sql:


> Linda



> > Hi All,
> > I have problem to delete a group of tables with drop table (table_name).
> > I have made temporary user tables and now i want to delete that tables.
> Each
> > of them looks like:
> > DOK_(user_name)
> > for example:
> > DOK_Gua
> > DOK_Zua
> > DOK_Mal, etc.
> > I have tried to make a stored procedure like this one:
> > DECLARE my_cursor CURSOR FOR
> > select name from sysobjects where name LIKE 'DOK[_]%'
> > OPEN my_cursor
> > FETCH NEXT FROM my_cursor


> > BEGIN

> >     FETCH NEXT FROM my_cursor
> > END

> > ... but it didn't work :(( It seems that drop table have to have direct
> > table name.

> > Any ideas how to do it ? or what i have missed in that script ?

> > Thanks a lot for any support
> > Wiht best regards
> > Dariusz Hoszowski

 
 
 

Deleting group of tables by drop table ?

Post by Joe Celk » Mon, 21 Jan 2002 05:11:16


This is bad programming.  

1) Temporary tables as implemented in SQL Server are proprietary.  

2) You should never have a group of tables with the same structure.  A
table is a set of things.  You are probably trying to use them as
instances of a class -- OO programmers misunderstand that part of data
modeling.

3) Allocate a permanent table with indexes, constraints and all those
wonderful things that keep data safe.  Then add a column for the current
user's identifier.  When you are finished with him, delete just his
rows.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Deleting group of tables by drop table ?

Post by Erland Sommarsko » Tue, 22 Jan 2002 08:29:50



> This is bad programming.  

> 1) Temporary tables as implemented in SQL Server are proprietary.  

Joe, do you read the posts, or are you just a program that triggers on
certain word in the articles?

It was completely clear from the posting that he did not have
temporary tables of the Sybase/Microsoft fame. They were just
temporary in the sense that he created them some time ago for some
reason, but they were meant to remain.

Quote:> 2) You should never have a group of tables with the same structure.

And, Joe, this guy *is* doing his homework: he is dropping all these
tables.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

 
 
 

Deleting group of tables by drop table ?

Post by Erland Sommarsko » Tue, 22 Jan 2002 08:31:07



> DECLARE my_cursor CURSOR FOR
> select name from sysobjects where name LIKE 'DOK[_]%'
> OPEN my_cursor
> FETCH NEXT FROM my_cursor


> BEGIN

>     FETCH NEXT FROM my_cursor
> END

> ... but it didn't work :(( It seems that drop table have to have direct
> table name.

For that sort of one-off thing I would simply say:

  SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE 'DOK[_]%'

and then cut and paste.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

 
 
 

Deleting group of tables by drop table ?

Post by Steve Kas » Tue, 22 Jan 2002 12:52:08


Dariusz,

  If for some reason you prefer not to use Dynamic SQL,
there is another solution:


drop table DOK_Doomed


Steve


> siure - thanks lindawie



> > D.,

> > You need to use dynamic sql:


> > Linda



> > > Hi All,
> > > I have problem to delete a group of tables with drop table (table_name).
> > > I have made temporary user tables and now i want to delete that tables.
> > Each
> > > of them looks like:
> > > DOK_(user_name)
> > > for example:
> > > DOK_Gua
> > > DOK_Zua
> > > DOK_Mal, etc.
> > > I have tried to make a stored procedure like this one:
> > > DECLARE my_cursor CURSOR FOR
> > > select name from sysobjects where name LIKE 'DOK[_]%'
> > > OPEN my_cursor
> > > FETCH NEXT FROM my_cursor


> > > BEGIN

> > >     FETCH NEXT FROM my_cursor
> > > END

> > > ... but it didn't work :(( It seems that drop table have to have direct
> > > table name.

> > > Any ideas how to do it ? or what i have missed in that script ?

> > > Thanks a lot for any support
> > > Wiht best regards
> > > Dariusz Hoszowski

 
 
 

1. Drop Table == Delete Table?

The reason I ask, is because the server manager window continues to
display the table I have dropped, and I can review the data structure.
So it's obviously not _completely_ deleted.

What if I really don't want it around anymore?

Thanks,

Kyle Green

 Running NT 4.0 (build 1381)
 Using Virtual Access 4.0

2. O_SYNC option in SVR3 open system call

3. drop any table vs. delete any table

4. Visual Basic coder needed

5. instead of delete trigger delete data from table using execute and temporary table for deleted

6. Filemaker Pro v3

7. Delete/Drop a table

8. Date calculations in Unix

9. Delete/Drop a Table

10. DROP Table (and Recreate) vs. DELETE

11. Delete * vs. Drop and Create Table

12. Dropping column silently kills multi-coumn index (was [ODBC] Error when accessing tables with deleted columns)

13. Delete vs Drop table and Create