Many a times we get into the situation where we need to do a specific task or run T-SQL Command for all tables in a Database. For Example: Disable all Check constraints/ Triggers on all tables.
This can be achieved by number of ways:
- Cursors
- Scripting a user defined loop
- Using sp_MSforeachtable
Out of all these using undocumented sp_MSForEachTable is the easiest way out. This sp is available in the master database. This SP loops through each table in the current DB and execute the script which is passed as a parameter.
For Example: If you need to disable all triggers for all tables in the database, you can use the below mentioned script.
USE Test_DB; Exec sp_MSforeachtable 'alter table ? disable trigger all';
? = it will work as a place-holder for table name, and get replaced in the run time.
You will pass your query in single quotes [‘’]
Example 2: The following script will disable all constraints on all tables in the database.
USE Test_DB; Exec sp_MSforeachtable 'alter table ? nocheck constraint all';
Likewise, we have sp_MSForEachDatabase which can be used in order to execute a script for all databases on a server.
Hopefully, this will help you.
Regards
Sarabpreet Anand
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
