Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-18 : 09:49:55
|
| Mark Janikiewicz writes "I have a database with approx 500 tables. The requirement is to delete all rows from every table.Is there a quick method for doing this rather than manually running a delete / truncate on every table.Or is it possible to produce a script to carry outVersion is SQL Server 7Windows NT 4 Thanks in advance for any suggestions." |
|
|
dsdeming
479 Posts |
Posted - 2002-04-18 : 09:56:34
|
| You could cursor through the INFORMATION_SCHEMA.TABLES view and dynamically issue a truncate table command for each user table. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-18 : 10:15:26
|
There is also an undocumented stored procedure called sp_MSforeachtable, you could use it like this:EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?'The question mark (?) is a placeholder for the table name. This procedure does exactly what dsdeming described; it cursors through all the table names and executes your command on each one.If you want more information on this and other undocumented functions, pick up Ken Henderson'sThe Guru's Guide to Transact-SQLorThe Guru's Guide to SQL Server Stored Procedures, XML and HTMLOr pick up both!  They're worth twice what you'll pay for them. |
|
|
|
|
|
|
Truncating / deleting from all tables