Click here to hide categories Click here to show left categories

User: Home          welcome : Guest          Log In / Register here     




Script to Truncate All Tables in a Database

This script truncate all tables or reset all tables of a database. In this script a loop is running through a cursor which first get the list of all tables generated by user and then truncate all the tables.

Declare @t varchar (1024)
Declare tbl_cur cursor for
select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tbl_cur

FETCH NEXT from tbl_cur INTO @t

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('TRUNCATE TABLE '+ @t)
FETCH NEXT from tbl_cur INTO @t
END

CLOSE tbl_cur
DEALLOCATE tbl_Cur

Note: Please take back of all data which you do not want to be deleted. This is for your data safety purpose.

Helpful Website Url
http://www.databasejournal.com/scripts/article.php/1497671/Script-to-Truncate-All-Tables-in-a-Database.htm
Share this article   |    Print    |    Article read by 8870 times
Author:
Rohit kakria
I am software developer
Related Articles:
Related Interview Questions: