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


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

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


Exec Sp_msforeachtable @command1='Truncate Table ?'


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


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



Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...