One of the most frequent request I get is for a procedure that can return sp_SpaceUsed type information for every table in a SQL Server 2005 database. This is easy enough to do with cursors and dynamic SQL, but after looking at how sp_SpaceUsed worked and how SMO gets the same information, I decided that I could write it without either.
Even better, once I wrote it though, I realized that it could easily be rewritten as a view. Now I could reuse it by joining it with other tables and views in new queries or procedures whenever I wanted. Enjoy!