I can't tell you how many countless times I loaded up a saved T-SQL cursor script to do some sort of looping over all the user tables in a database.  Well, APPARENTLY there is an undocumented system sproc named "sp_MSforeachtable" which does exactly what it says....it will do something for each table in the current database.  I stumbled upon this over at Joe Webb's blog

Here is an example of its use.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'SELECT COUNT(*) FROM ?';

There are obviously easier ways to get table counts but I just used this as an easy example.  The "?" is a special character that gets replaced by the actual table name.  Also, the T-SQL command in quotes must be something that can be executed.  Joe uses examples such as checking the amount of space each table is using.  This can really come in handy for doing those "maintenance" tasks on all your tables in a database quickly and easily.  You could write an SSIS package or a scheduled task to perform all this maintenance nightly using the sproc above.

Posted in:   Tags:
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017 Tony Testa's World