truncate table

Question

Hi Friends , in Sql Server 2008r2 I have database (Employee ) having 1000 table i want to truncate all table at a time if it is possible through sql query or store dprocedure Please let me know

Jignesh Trivedi · Answer

hi, Truncate table may not work with foreign key constrain so you have to perform delete operation. there is one undocumented feature of SQL server call sp_msforeachtable, using this SP you can perform for each loop on tables try... http://mssqltrek.com/tag/truncate-all-tables-in-a-sql-server-database/ hope this will help you.

Abhay Shanker · Answer

Try below script Declare @t varchar (1024)Declare tbl_cur cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'OPEN tbl_curFETCH NEXT from tbl_cur INTO @tWHILE @@FETCH_STATUS = 0BEGINEXEC ('TRUNCATE TABLE '+ @t)FETCH NEXT from tbl_cur INTO @tENDCLOSE tbl_curDEALLOCATE tbl_Cur for more details check below urls http://www.databasejournal.com/scripts/article.php/1497671/Script-to-Truncate-All-Tables-in-a-Database.htm http://stackoverflow.com/questions/6028960/sql-truncate-database-how-to-truncate-all-tables

Anupam Singh · Answer

Hi Ravi, Try this trick : select 'TRUNCATE TABLE '+name from sys.tables where type_desc='USER_TABLE' // OR YOU CAN SPECIFY TABLE USING WILDCARD Copy result and execute it on SSMS. Hope it helps.

truncate table

Answers (3)