Here is what it does:1) Disable all the constraints/triggers for all the tables2) Delete the data for each child table & stand-alone table3) Delete the data for all the parent tables4) Reseed the identities of all tables to its initial value.5) Enable all the constraints/triggers for all the tables.

Note: This is a batch t-sql code which does not create any object in database. If any error occurs, re-run the code again. It does not use TRUNCATE statement to delete the data and instead it uses DELETE statement. Using DELETE statement can increase the size of the log file and hence used the CHECKPOINT statement to clear the log file after every DELETE statement.

Imp: You may want to skip CHECKIDENT statement for all tables and manually do it yourself. To skip the CHECKIDENT, set the variable @skipident to "YES" (By default, its set to "NO")

Usage: replace #database_name# with the database name (that you wanted to truncate) and just execute the script in query analyzer.

/* Step 2: Delete the data for all child tables & those which has no relations*/

While exists ( select T.table_name from INFORMATION_SCHEMA.TABLES T left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and T.table_name not in ('dtproperties','sysconstraints','syssegments') and Table_type='BASE TABLE' and T.table_name > @TableName )

Begin Select top 1 @tableOwner=T.table_schema,@tableName=T.table_name from INFORMATION_SCHEMA.TABLES T left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and T.table_name not in ('dtproperties','sysconstraints','syssegments') and Table_type='BASE TABLE' and T.table_name > @TableName order by t.table_name

Warning: The table empcontact' has been created but its maximum row size (17468) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Warning: The table 'empact' has been created but its maximum row size (17727) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Here is what it does:1) Disable all the constraints/triggers for all the tables2) Delete the data for each child table & stand-alone table3) Delete the data for all the parent tables4) Reseed the identities of all tables to its initial value.5) Enable all the constraints/triggers for all the tables.

Note: This is a batch t-sql code which does not create any object in database. If any error occurs, re-run the code again. It does not use TRUNCATE statement to delete the data and instead it uses DELETE statement. Using DELETE statement can increase the size of the log file and hence used the CHECKPOINT statement to clear the log file after every DELETE statement.

Imp: You may want to skip CHECKIDENT statement for all tables and manually do it yourself. To skip the CHECKIDENT, set the variable @skipident to "YES" (By default, its set to "NO")

Usage: replace #database_name# with the database name (that you wanted to truncate) and just execute the script in query analyzer.

/* Step 2: Delete the data for all child tables & those which has no relations*/

While exists ( select T.table_name from INFORMATION_SCHEMA.TABLES T left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and T.table_name not in ('dtproperties','sysconstraints','syssegments') and Table_type='BASE TABLE' and T.table_name > @TableName )

Begin Select top 1 @tableOwner=T.table_schema,@tableName=T.table_name from INFORMATION_SCHEMA.TABLES T left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and T.table_name not in ('dtproperties','sysconstraints','syssegments') and Table_type='BASE TABLE' and T.table_name > @TableName order by t.table_name

Using SQL Server 2008 R2 express: I have been trying/looking for a way to automate a process that takes multiple tables from an Access database (downloaded from a website and updated weekly by a state agency)and imports them into SQL Server Express (testing environment); but I need to do this on a fairly regular basis (usually weekly) so I need to delete the data in the existing SQL Server tables before I import from Access.So far so good; I tested pvsramu's code and voila! That tales care of deleting all of the existing data. There are no constraints on the tables because I built the views to pull together what is needed to link to another in house Access database which is where the users want to work with the data. I know if I use the regular edition of SQL server I can save the import package; I am guessing I can save pvsramu's code as a script; can I somehow pull this all together into a neat little job?