Print big

A feature of T-SQL is that strings longer than 8000 bytes are truncated by PRINT. If you haven't already discovered this, you might wonder why it's a problem – the answer (for me at least) is dynamic SQL.

Dynamic SQL is a great way to generate boilerplate SQL code repeatably without errors. This SQL 2017+ example generates a statement to insert the contents of DMV sys.databases into a pre-existing temporary table:

SET NOCOUNT ON
 
DECLARE @sql NVARCHAR(MAX)
 
SELECT 
  @sql = 'INSERT INTO #databases (
  ' + CAST(STRING_AGG(QUOTENAME([name]), CHAR(13) + CHAR(10) + ', ') AS NVARCHAR(MAX)) + '
)
SELECT
  ' + CAST(STRING_AGG(QUOTENAME([name]), CHAR(13) + CHAR(10) + ', ') AS NVARCHAR(MAX)) + '
FROM sys.databases'  
FROM sys.all_columns
WHERE [object_id] = OBJECT_ID('sys.databases')
 
PRINT '-- statement length = ' + CAST(LEN(@sql) AS NVARCHAR(20))
PRINT @sql

The output of PRINT @sql ends like this:

, [is_memory_optimized_elevate_to_snapshot_on]
, [is_federation_member]
, [is_remote_

This is clearly not the end of a valid SQL statement! PRINT has truncated the @sql variable at 4000 characters (i.e. 8000 bytes of Unicode).

One approach to the problem is to print the string in chunks of exactly 4000 characters, but this introduces arbitrary line breaks – so the output SQL code is likely still to be invalid. Instead we need an approach that respects existing line breaks.

This script prints strings in chunks of up to 8000 bytes – the only line breaks are those present in the original text. ¹⁾

DECLARE @text NVARCHAR(MAX) = 'YourTextHere'
 
/* (to create the SP, just comment out or delete *this line*!)
IF OBJECT_ID('tempdb..#printBig') IS NOT NULL
  DROP PROCEDURE #printBig
 
GO
CREATE PROCEDURE #printBig (
  @text NVARCHAR(MAX)
)
AS
--*/
DECLARE @lineSep NVARCHAR(2) = CHAR(13) + CHAR(10)  -- Windows \r\n
DECLARE @maxLen INT = 4000
 
DECLARE @lastLineSep INT
DECLARE @len INT
DECLARE @off INT = 1
 
WHILE @off < LEN(@text)
BEGIN
 
  SET @lastLineSep = CHARINDEX(REVERSE(@lineSep), REVERSE(SUBSTRING(@text, @off, @maxLen + LEN(@lineSep))))
  SET @len = @maxLen - CASE @lastLineSep WHEN 0 THEN 0 ELSE @lastLineSep - 1 END
  PRINT SUBSTRING(@text, @off, @len)
  SET @off += CASE @lastLineSep WHEN 0 THEN 0 ELSE LEN(@lineSep) END + @len
 
END

Now I replace PRINT with a call to #printBig:

--PRINT @sql
EXEC #printBig @sql

and the end of the output looks much better:

, [is_memory_optimized_elevate_to_snapshot_on]
, [is_federation_member]
, [is_remote_data_archive_enabled]
, [is_mixed_page_allocation_on]
, [is_temporal_history_retention_enabled]
FROM sys.databases