August 10, 2004 at 3:24 pm
August 10, 2004 at 6:36 pm
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
August 12, 2004 at 7:00 am
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.
http://www.databasejournal.com/scripts/article.php/1491751
You could use the temporary table created by this routine to generate deletes.
David Bird
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply