0

I'm running a query that create the command to drop some indexes from some tables, I've ran it filtering 21 tables (each table must have around 30 indexes). I've noticed the output results didn't show them all. I've counted around 800 lines results.

I've tried to put the PRINT as NTEXT, tried to output a '.rpt' file, maximized the characters limit in results to text options on SQL Server.

Got any idea how I can solve this?

Sample query:

DECLARE @sql nvarchar(max) = N'USE ' + QUOTENAME(db_name()) + ';' + char(13) + N'GO';
    
SELECT @sql += char(13) + char(13) + N'DROP INDEX ' 
      + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) 
      + '.' + QUOTENAME(t.name) + ';' + char(13) + N'GO'
FROM
    sys.indexes i
JOIN 
    sys.tables t ON t.object_id = i.object_id
JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
WHERE
    t.Name IN ('Table1', 'Table2', 'Table3')
    AND i.name IS NOT NULL 
    AND i.name NOT LIKE '%_I00';
      
PRINT CAST(@sql AS NTEXT);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
carloshnrq
  • 13
  • 3
  • Specifically this `SELECT CAST(@MyLongString AS XML)` should do the trick. – Alex May 25 '22 at 05:26
  • I've tried to export as XML and NTEXT, both didn't came with all results, like this: https://ibb.co/djsYhtZ – carloshnrq May 25 '22 at 05:56
  • NO NO NO NO NO - NTEXT and TEXT have been deprecated for almost 25 years. NEVER use them in new code. – SMor May 25 '22 at 11:18

0 Answers0