SQL Server : how to print variable breaking each line and going above varchar(MAX)

Asked May 22 '19 at 18:31

Active May 22 '19 at 20:20

Viewed 32 times

I have the following query:

DECLARE @TableName VARCHAR(MAX)
DECLARE @Script VARCHAR(MAX) = ''
DECLARE @sql NVARCHAR(MAX)
DECLARE @fillfactor INT

DECLARE TableCursor CURSOR FOR
    SELECT '[' + OBJECT_SCHEMA_NAME([object_id]) + '].[' + name + ']' AS TableName
    FROM sys.tables

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD' + ')' 

    SELECT @Script = CAST(@Script + @sql AS VARCHAR(MAX))

    FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

PRINT @Script
GO

New to SQL Server. Right now it is limited to 8000 characters, I wanted to know how can I print @Script with all the tables and at the same time each line on @sql break into a new line.

edited May 22 '19 at 20:20

marc_s

732,580
175
1,330
1,459

asked May 22 '19 at 18:31

Nickso

Why don't you simply print on each loop iteration? – Luis Cazares May 22 '19 at 18:39
You right... Now onto the VARCHAR(MAX) one. Do you have any idea how can I go above 8k characters? – Nickso May 22 '19 at 18:42
In SQL Server Management Studio, you cannot. No more than 8k will be printed or displayed in one go. See https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement for an alternative. – TT. May 22 '19 at 18:43
Your code already stores more than 8k characters. Check the length with `SELECT LEN( @Script)` – Luis Cazares May 22 '19 at 18:45
Remove the closing parenthesis and add a line feed and/or carriage return. – Luis Cazares May 22 '19 at 18:46
`VARCHAR(MAX)` can store up to **2 billion** characters (not just 8000) ..... – marc_s May 22 '19 at 20:20

SQL Server : how to print variable breaking each line and going above varchar(MAX)

0 Answers0