Two options that seem to be missing, and I think are both more appropriate than most options listed so far:

• Poor Man's T-SQL Formatter / poorsql.com (free, open-source, SSMS add-in, command line formatter, full DDL / DML script formatting, and I'm the author/maintainer, so I get to put it first :))
• T-SQL Tidy (free, SSMS Add-In, full DDL/DML script formatting, apparently an SQL Server 2008 R2 add-in can do offline formatting also now - but not open-source?)

I believe either of these makes more sense than any of the previous answers because the other options provided are:

• Online-only (instant SQL formatter)
• Trialware / commercial software (SQL pretty printer)
• Don't format (SSMS tools pack, the accepted answer)
• Don't handle full T-SQL properly ( CPAN module, SQL-talk prettifier)
• Incomplete, only handle some statements (SqlFormat project)

ssmstools is useful

I use Instant SQL Formatter occasionally. It does the job for me.

The format() function in python-sqlparse works nicely for me.

You could try the free Simple Talk Prettifier.

It's basic, but it might satisfy your requirements.

An SSMS 2005/2008 addin: http://code.google.com/p/sqlformat/

It lacks some features, but it is very a good start.

SQL::Beautify may or may not help (a Perl module). I haven't tried it.