John Esraelo (2/13/2008)
I like using sp_msforeachtable for certain quick and dirty works.sp_msforeachtable as you know is a base function / sp that overloads some requests, meaning you can run other functions thru this sp.
You are absolutely right, there are times that you don't want to make and push the server do silly things and waste the system resouces.
One of the usages for this sp would be something in the line of indexing all the table at 2:00 am and I have tried that with couple of hundred tables and two or three indexes on each table and an average of few 10s of thousands records in 25 seconds.
Your experience shows that you have dealt with many great databases and obviously I can learn few things from you.
thx for the reply and looking forward in learning more cool things from you in SQL Server Central.
John Esraelo
John,
Thanks for the compliment; I just have different experiences. I am sure I can, and will, learn as much from you.
I agree with 100% that SP_MSForEachTable is a great method for small / non-complex tasks that require iterating throughout the database. Indexing, believe it or not, is not something I've thought of using it for...go figure, I've used the good ol' fashioned SSIS Scripting trick...but, I've also liked the ability to log the results (Successful or Failure) to a flat file for later review. I'll definitely give SP_MSForEachTable a shot on one of my test systems to see the benefits.
I look forward to many, many future discussions with you as well.
Thanks,
James Rea
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/