Easiest way to delete all records for all tables?

Easiest way to delete all records for all tables?

Post by jame » Fri, 19 Apr 2002 05:13:40



Something like

DELETE * FROM *

excpet only the tables I have created, not the 'system' tables

thanks,

james

p.s.  Is there a reference that describes all the master stored procedures
and extended stored procedures??

 
 
 

Easiest way to delete all records for all tables?

Post by oj » Fri, 19 Apr 2002 05:22:48


every single delete statement will be logged. 'truncate table' might help
but there is limitation - see book online for more info. if you must you
could use undocumented 'sp_msforeachtable' to cycle through and
delete/truncate.

the fastest/cleanest, imho, is to use EM and generate the script for the
entire db with constraints/indexes/etc and run it.

--
-oj

http://www.rac4sql.com


Quote:> Something like

> DELETE * FROM *

> excpet only the tables I have created, not the 'system' tables

> thanks,

> james

> p.s.  Is there a reference that describes all the master stored procedures
> and extended stored procedures??


 
 
 

Easiest way to delete all records for all tables?

Post by Anith Se » Fri, 19 Apr 2002 05:29:56




 DECLARE DEL_CUR CURSOR FOR
      SELECT
           [name]
      FROM
           sysobjects
      WHERE
           type = 'U'
 OPEN DEL_CUR
 FETCH NEXT FROM DEL_CUR


  BEGIN


   FETCH NEXT FROM DEL_CUR
  END
 CLOSE DEL_CUR
 DEALLOCATE DEL_CUR

Execute the above script. Hope it fits your needs
Thanks
Anith


Quote:> Something like

> DELETE * FROM *

> excpet only the tables I have created, not the 'system' tables

> thanks,

> james

> p.s.  Is there a reference that describes all the master stored procedures
> and extended stored procedures??

 
 
 

Easiest way to delete all records for all tables?

Post by jame » Fri, 19 Apr 2002 05:38:40


This just occurred to me,

What about just generating the SQL Scripts

EnterpriseManager->Database->All Tasks->Generate SQL Scripts,

then execute the SQL on a new database?

thanks,

james




>  DECLARE DEL_CUR CURSOR FOR
>       SELECT
>            [name]
>       FROM
>            sysobjects
>       WHERE
>            type = 'U'
>  OPEN DEL_CUR
>  FETCH NEXT FROM DEL_CUR


>   BEGIN


>    FETCH NEXT FROM DEL_CUR
>   END
>  CLOSE DEL_CUR
>  DEALLOCATE DEL_CUR

> Execute the above script. Hope it fits your needs
> Thanks
> Anith



> > Something like

> > DELETE * FROM *

> > excpet only the tables I have created, not the 'system' tables

> > thanks,

> > james

> > p.s.  Is there a reference that describes all the master stored
procedures
> > and extended stored procedures??

 
 
 

1. Cannot add record to Visual Foxpro table after deleting all records in table

After:
1. opening adodb connection with
vfpoledb.dll and datasource=path$ &
foxprofile.dbc successfully
2. deleting all records from table1 in
foxprofile.dbc successfully with
connection.execute("delete from table1") (I
can see the records being marked as deleted
in the VFP IDE) ,

I cannot add any record to table1 that has a
primary key like one of the deleted records.
So I execute connection.execute("pack
table1") and get "ADO error: File is in use".
(but my foxpro IDE is closed and only my VB6
program has the *.dbc open).

How can I delete (and pack if necessary) all
the records so they are gone for good from
the table?

Please!
Thank you.

2. Incredible Performance gain by Dump & Load with client-server on NT

3. Deleted records when text file is linked as table (#deleted)

4. Scheduling Universe Tasks

5. ado .delete deletes joined table records?

6. Developer Wanted

7. SQL7 server crawls (locks) when deleting records from a 3 million record table

8. Rule-Based Test Data Generator

9. deleting records in a table and then adding new records

10. Deleting Records In One Table Based on records in Another

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

12. Error 3167 Record Deleted when there is no deleted record

13. Delete from one table with matching records in another table