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

5anewei6 于 8个月前发布在 SQL Server

关注(0)|答案(3)|浏览(76)

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'

sql-server

来源：https://stackoverflow.com/questions/1975468/how-do-i-include-the-newlines-from-syscomments-in-sql-server

3条答案

按热度按时间

b4wnujal1#

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

No copy paste needed :)

赞(0）回复(0）举报 8个月前

vybvopom2#

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)?

赞(0）回复(0）举报 8个月前

egdjgwm83#

in ssms 19 switch on "retain CR/LF on copy or save" option in (tools > options) query results > sql server > results to grid . that should fix it.

赞(0）回复(0）举报 8个月前