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:
Tony Testa posted on September 16, 2007 00:00

(this post has been a long time coming since i never had a blog)

I gave a SQL Server Integration Services talk at the Monthly Philly.Net Users group on 6/20/2007.

The talk went well.  The room was packed...i'd say about 50+ people.  My Company, Perficient Inc. sponsored the event and a fellow co-worker of mine Afshin Zavareh gave a talk as well on Sharepoint 2007 Features

My talk went over what SSIS was, how it replaces DTS, and then I went into some demos of how to use SSIS for some common ETL operations.

The presentation files are located here.

I learned a great deal about SSIS at the client that I am presently at, so I had plenty of experience with it, roughly 6months+ working with it fulltime.

I realized after giving the talk that I could easily do another talk on Advanced SSIS topics, such as logging, configuration files, etc.  I am going to try to come up with another presentation of those topics so that I can present it at another meeting.

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