How do I include the newlines from syscomments in SQL Server?

Question

I'm building a script to get all the triggers in the document, but I want to include newlines. I'm copying and pasting this into excel in order to then quickly generate some mandated documentation, but all the contents from the text column end up being generated without newlines.

The line I'm currently using, which produces truncation in results to file is:

SELECT OBJECT_DEFINITION(sys.objects.object_id)
FROM sys.objects 
WHERE type = 'TR'

Answer 1

Tools->Options->Query Results->Results to file

No copy paste needed :)

Answer 2

For one, don't use syscomments on SQL Server 2005+. Use sys.sql_modules or OBJECT_DEFINITION. The datatype in syscomments in nvarchar(4000) which means truncation.

If you're in:

• "Results to grid", you'll never have newlines
• "Results to text", you'll have truncation (in tools..options somewhere)

So, why not use one of these techniques:

• bcp or sqlcmd using OBJECT_DEFINITION on sys.objects to generate a plain text file?
• use a SQL query in Excel?
• SMO?
• "Results to file" (as jvenema mentioned)?