Is this syntax correct?

• Ram Ganesan

  SSC Enthusiast

  Points: 146

  More actions

  August 11, 2004 at 8:05 am

  #86046

  Hello,

  I am trying to delete all records in all tables in a single database. Which of these stored procedure syntax is correct for doing that?

  exec sp_msforeachtable 'truncate table tablename'

  or

  exec sp_msforeachtable 'delete table tablename'

   

  Also...I am assuming that these stored procedures would NOT alter any of those sytem(sys) tables in the database.

   

  Thanks.

  Ram

  ---

  Thanks,

  Ram Ganesan
  

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  August 11, 2004 at 8:13 am

  #518503

  Do not use the second version, that will actually delete the table.

  The first is roughly correct, but you need to used to parm coming back from "ForEachTable" instead of TableName

   

  It's a little tricky, so I don't gaurantee this is exact.

  exec sp_msforeachtable 'truncate table "?"'

  The "?" with double quotes does the parameter replacement.

  
  KlK