We will get the brief idea about undocumented procedure sp_MSforeachtable of the Master database.sp_MSforeachtable can be used to loop through all the tables in your databases.
Some of the basic usages of this stored procedures are:-
1. Display the size of all tables in a database
USE NORTHWIND
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
2. Display Number of Rows in all Tables in a database
USE ANYDBNAME
EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'
3. Rebuild all indexes of all tables in a database
USE ANYDBNAME
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
4. Disable all constraints of all tables in a database
USE ANYDBNAME
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
5. Disable all Triggers of all tables in a database
USE ANYDBNAME
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
6. Delete all data from all tables in your database
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
To RESEED all table to 0, use this script
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)
'
GO
7. Reclaim space from dropped variable-length columns in tables or indexed views
USE ANYDBNAME
EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';
8. Update Statistics of all Tables in a database
USE ANYDBNAME
EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'
Please comment if you have any query.
Please comment if you have any query.
