There may be situations where you need to delete all the tables in a database, or clean all rows in all the tables or truncate all the tables in database. If the database contains only few tables then it is not a problem, Otherwise it would be a major task to clean the database.
Luckily Microsoft SQL Server has an option of navigating to all tables using a stored procedure sp_MSforeachtable
For e.g the following command will drop all the tables in the database
exec sp_MSforeachtable 'DROP TABLE ?'
The below command will clear all the tables, Leaving the table clean
exec sp_MSforeachtable 'DELETE FROM ?'
So by using the procedure sp_MSforeachtable you can navigate to all the tables and do your task like cleaning table, dropping table etc.
Related Posts
- Clear Query Cache in SQL Server
- SQL Server 2005 database DBA Checklist for database administrator
- Oracle PL/SQL: Oracle System Tables
- Guidelines for Testing Database Level Application
- SQL Compare – Compare and Synchronize tool for SQL Server Database
Tags: Clear all tables in SQL Server Database, Drop All tables in SQL Server Database, sp_MSforeachtable, Truncate all tables in SQL Server Database
is it an existing procedure in the database?
Yes “sp_MSforeachtable” is an SQL Server Undocumented Stored Procedure.
Bless you TechGuy
How can we delete all stored procedures in the database? Any idea ?
Yes the sp_MSforeeachtable is very powerful SP, but it would be better if you post some tips and tricks to use it with some example (maybe 10 cases with this very nice SP)!
Thanks!
You can delete all stored procedures by reading this Article http://www.devasp.net/net/arti.....y/309.html