Useful operations with sp_MSforeachtable Stored Procedure

Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase:
---------------------
--Delete all data in the database
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

EXEC sp_MSForEachTable
'BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
DELETE FROM ?
END CATCH;'
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
---------------------
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable 'DELETE ?'
---------------------
--Drop all Tables
exec sp_MSforeachtable 'DROP TABLE ?'
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
---------------------










Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase with printed message in Messages window of SQL Server Management Studio:
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'"
exec sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL PRINT'? trigger altered'"
---------------------
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable "DELETE ? PRINT'? deleted'"
--Delete all data in the database
EXEC sp_MSForEachTable "DELETE FROM ? PRINT'? deleted'"
EXEC sp_MSForEachTable "TRUNCATE TABLE ? PRINT'? truncated'"
EXEC sp_MSForEachTable
"BEGIN TRY
TRUNCATE TABLE ? PRINT'? truncated'
END TRY
BEGIN CATCH
DELETE FROM ? PRINT'? deleted'
END CATCH;"
---------------------
--Drop all Tables
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped'"
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL PRINT'? constraint altered'"
exec sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL PRINT'? trigger altered'"
---------------------








Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase with printed message in Messages window of SQL Server Management Studio and for a particular schema:
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? DISABLE TRIGGER ALL PRINT'? trigger altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------
--Drop table of particular shcemaID/shemaName
Exec sp_MSforeachtable
@command1 = "DROP TABLE ? PRINT '? dropped'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? CHECK CONSTRAINT ALL PRINT'? constraint altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? ENABLE TRIGGER ALL PRINT'? trigger altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------








Following displays how to execute Drop operation on a DataBase for a particular schema and with Like condition:
---------------------
--Drop table of particular shcemaID/shemaName and with name starting with 'Temp_'
Exec sp_MSforeachtable
@command1 = "DROP TABLE ? PRINT '? dropped'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')
and o.name LIKE 'Temp_%'"
---------------------




Source: http://avinashkt.blogspot.com/2008/05/useful-operations-with-spmsforeachtable.html