How to delete records in all tables in a single database

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

tomasz stawarz Grasshopper Points: 10 More actions · Answer 1

Here is an example how to export all tables with DTS: http://www.sqldts.com/default.aspx?299

Just a little change and u have what u want.

Enjoy

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 2

Here is a SQL Script I use to truncate tables. If the tables have foreign keys you will need to use DELETE instead of Truncate.

/* This routine delete all rows for each table in a database

BE CAREFUL

If the table has foreign keys, you need to Use DELETE instead of Truncate

*/

set nocount on

/* Drop temporary table if exist */

declare @Tbschema varchar(255), @Tbname varchar(255), @Statement varchar (2000)

/* Get list of all User tables */

DECLARE User_Tables_Cursor CURSOR FOR

select table_schema, table_name

from information_schema.tables

where table_type = 'base table'

and table_name <> 'dtproperties'

OPEN User_Tables_Cursor

FETCH NEXT FROM User_Tables_Cursor INTO @Tbschema, @Tbname

WHILE @@FETCH_STATUS = 0

BEGIN

Select @Statement = 'Select ''' + @Tbname + ''', COUNT (*) FROM ' + @Tbschema + '.' + @Tbname

+ ' ;'

-- Select @Statement = 'truncate table ' + @Tbschema + '.' + @Tbname + ' ;'

EXEC (@Statement)

FETCH NEXT FROM User_Tables_Cursor INTO @Tbschema, @Tbname

END

CLOSE User_Tables_Cursor

DEALLOCATE User_Tables_Cursor

If you need to delete the tables in reverse Hiearchial Order take a look at this routine which generates a report in hierachial order.

You could use the temporary table created by this routine to generate deletes.

David Bird