Getting the full explain plan for long queries

I was recently debugging a large stored procedure (2000+ lines of code) for a customer. I wanted to look at the explain plan for a particularly long SELECT statement in the sproc right in the middle for optimal table design. I opened the statement in SSMS, copied in the text, added the EXPLAIN keyword in front, and hit F5. Waited for my results and ....wait a minute, the text is truncated.

You're natural reaction might be to go to the Grid Result options in SSMS and change the values. This won't help as the maximum non-XML result set within SSMS doesn't allow you to see all the data. So what do you do? SQLCMD to the rescue. You can simply run the statement at the command line and have sqlcmd return the results as a file. The key is the -y and -o parameters. The -y parameter is used to set the variable_length_type_display_width value. The -o parameter is used to specify the output file to write to. If you put these two together with the secret -y 0 (to return all of the data), you have magic. An example of how to use this:

sqlcmd.exe -S -d -U -P -I -y 0 -o explain.txt -Q "EXPLAIN SELECT * FROM table"

If you open the explain.txt file, you'll see the full EXPLAIN plan for the query.