Article SQL Server Undocumented procedure sp_MSforeachtable & sp_MSforeachdatabase
| Undocumented procedure sp_MSforeachtable & sp_MSforeachdatabase |
 |
 |
 |
| SQL Server - Articles | ||||||||
| Written by S. Singh | ||||||||
|
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:
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 <your_DB>;
? = 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 <your_DB>;
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 Singh
Only registered users can write comments!
Powered by !JoomlaComment 3.26
3.26 Copyright (C) 2008 Compojoom.com / Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."
|
S. Singh
Sarabpreet’s Seven years of enriched experience and intellectual skills, have provided him an edge over the others by way of endowing him an opportunity to work and grow along with industry leaders. The keen urge of continuously learning and growing to evolve himself and being adaptable to changing environment has proved to be a milestone for his carrier by way of doing certification for database administrator from the I.T Industry leader Microsoft. His core competency lies in administration of SQL Server 2000, 2005 & 2008.
Presently Sarabpreet is working as a Freelancer and provides support to Multiple Clients Handles more than 90 servers & 800+ Databases, all responsibilities related to same such as Designing High Availability Database Solutions Clustering, Log-shipping and Mirroring, Backup\ Restore (Disaster Management), Automating SQL Server monitoring and improving current processes, Identifying performance bottlenecks and providing improvement recommendations, Installation and configuration of SQL 2000 and 2005 cluster, Configuring and troubleshooting backup/DR jobs, Implementing database change requests received from developers, Resolving performance issues, performance tuning, Database Administration and resolving day-to-day issues.
