Team Foundation Server 2010 - Update content of a file being checked in

Having found the developer blogosphere increasing useful as a source of information for my daily tribulations as a developer, I've decided it's time to contribute something back to the community. Hopefully someone else can stumble upon a useful nugget here that would help them on their way to solving some problem the same way googling has led me to the blogs of countless other developers sharing their bits and pieces.

Recently a requirement came up to insert/update a version number in .sql files on check-in for some Team Projects. When a clients DBA's are looking at stored procs in dev/test/live environments they've got no easy way of correlating the installed version in SQL to a version in TFS besides comparing the actual contents of the stored proc itself. An existing version comment section required manual updates by any developer making a change, mildly error prone as you can imagine. Any sort of automatic version number in the .sql file would ensure the ability to track back to TFS changesets and work items. This should be available as a TFS Check-in Policy so that it could be enabled/disabled for individual Team Projects.