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
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Jignesh TrivediPosted May 22, 2014, 3:02 AM
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 ShankerPosted May 22, 2014, 2:21 AM
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 SinghPosted May 22, 2014, 1:53 AM
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.