Jan 12, 2017

SQL Server - How can we truncate all tables in Database?

sp_msforeachtable can be use to truncate tables from database. sp_msforeachtable is undocumented means this is not documented by Microsoft it can be change or modified at any time. It contains two parameters @command1 and @whereand using this procedure we can truncate tables.

If you want truncate all tables in database then use following query.

Exec Sp_msforeachtable @command1='Truncate Table ?'

If you want to truncate all tables of particular schema then use following query

Exec Sp_msforeachtable @command1='Truncate Table ?',@whereand='and Schema_Id=Schema_id(''schema_name'')'