SQL Server provides a wide range of built-in stored procedures that can simplify and automate various tasks. One such undocumented procedure is sp_MSforeachtable in the Master database. This procedure allows you to loop through all the tables in your databases and perform actions on them.

Here are some basic usages of the sp_MSforeachtable stored procedure:

1. Display the size of all tables in a database

USE NORTHWIND
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

This query will display the size of each table in the NORTHWIND database.

2. Display the number of rows in all tables in a database

USE ANYDBNAME
EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) AS NumberOfRows FROM ?'

This query will retrieve the number of rows in each table of the specified database.

3. Rebuild all indexes of all tables in a database

USE ANYDBNAME
GO
EXEC sp_MSforeachtable @command1="PRINT '?' DBCC DBREINDEX ('?', ' ', 80)"
GO

This script will rebuild all the indexes of every table in the specified database.

4. Disable all constraints of all tables in a database

USE ANYDBNAME
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

By executing this query, you can disable all constraints for every table in the specified database.

5. Disable all triggers of all tables in a database

USE ANYDBNAME
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

This query will disable all triggers for each table in the specified database.

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

Executing this script will delete all data from every table in your database. It first disables referential integrity, then deletes or truncates the data, and finally enables referential integrity again.

7. Reclaim space from dropped variable-length columns in tables or indexed views

USE ANYDBNAME
EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS;'

This query will reclaim space from dropped variable-length columns in tables or indexed views in the specified database.

8. Update statistics of all tables in a database

USE ANYDBNAME
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH ALL'

By executing this query, you can update the statistics of all tables in the specified database.

These are just a few examples of how you can utilize the sp_MSforeachtable stored procedure to perform various tasks on your SQL Server tables. It is important to note that this procedure is undocumented and may not be officially supported by Microsoft. Therefore, it is recommended to use it with caution and thoroughly test it in your development environment before implementing it in a production environment.

Feel free to leave a comment if you have any queries or suggestions regarding the usage of sp_MSforeachtable.