I recently came across an issue where a database had a corruption and when trying to fix it using DBCC CHECKDB, it came back with the following error:
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
TempDB had autogrowth enabled and the disk was no where near out of space, so this error was not helping to identify the actual issue.
I needed to run a script on all tables in the database to try and pinpoint which table (or tables) had a corruption.
Rather than creating a script to use a cursor to loop through each table and execute a command, there’s an undocumented sp_MSforeachtable stored procedure in the master database that does it for you, but with considerably less coding!
I could then run:
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?], REPAIR_ALLOW_DATA_LOSS)';To check to see which tables had an issue.
This stored procedure is also useful if you wanted to rebuild all indexes in the database:
EXECUTE sp_MSforeachtable 'ALTER INDEX ALL ON [?] REBUILD';I’m the case of the database I was fixing; I found which two tables had issues and managed to reduce how much data was lost before being able to run CHECKDB successfully and bring the database back into normal operation.
I’m sure there are many other uses where this sp_MSforeachtable stored procedure will come in handy when you need something quick to loop through all tables in the database.