Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Validate the contents of large dynamic SQL strings in SQL Server

By:   |   Updated: 2014-03-24   |   Comments (2)   |   Related: More > Dynamic SQL


Often when you are generating dynamic SQL you will want to validate the content of the query as opposed to just blindly executing it. Unfortunately, Management Studio can make it difficult to check that your command is complete, never mind to troubleshoot any errors that may occur when you do execute the command.


When you are building a dynamic SQL command, there are various ways that you can look at the text of the command without actually executing it. Let's look at a simple example. Let's say I want to generate an UPDATE STATISTICS command against all of the tables in a database. A simple way to do this would be:

SET @sql = N'';
SELECT @sql = @sql + N'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id];
SELECT @sql;
-- EXEC sp_executesql @sql;

If I have 5 tables with relatively short names, this isn't a problem. The output (in Results to Text) looks like this:


The challenge comes when the command is longer. We can simulate this by creating tables with slightly longer names; I created 10 tables with the following naming pattern, and in Results to Text the output is:

UPDATE STATISTICS [dbo].[my_tables_have_really_long_and_obnoxious_names_01] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[my_tables_have_really_long_and_obnoxious_names_02] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[my_tables_have_really_long_and_obnoxious_name

Notice that - according to the select - the command gets truncated. This isn't actually the case, which we can verify using LEN() or DATALENGTH():

------  ------
   920    1840

However, if we manually check the length of the output we see that, including the leading CR+LF, it is 256 characters. This is no coincidence; after all, the default in Management Studio is to only show 256 characters per column in Results to Text output (Tools > Options > Query Results > SQL Server > Results to Text):

SSMS options for Results to Text

We can increase this, but only up to a total character count of 8,192. This will bring SELECT - in Results to Text at least - up to the same limit afforded by the PRINT command. Both are obviously still problematic if the resulting string is going to exceed 8K.

In Results to Grid, the output is a little harder to read, since the grid removes carriage returns and line feeds in order to fit the output into a single cell. Going back to my first five tables:

Grid results, all on one line.

All the content is there, which you can verify by highlighting the cell, copying it, and pasting it into a new query window. Even the carriage returns are restored. However, this can still be a limiting approach, since the output of the grid is restricted to a maximum of 64K (65,535 characters):

SSMS options for Results to Grid

There's a clue there about how we can move beyond this 64K barrier: use XML.


This generates the following output in the grid:

XML output

Some limitations here, too, though. For one, the only way to really look at the output is to click on the grid output; this will open in a new window, but it is not a true query window - there will be no color highlighting, and you can't execute from this window either:

XML output,once clicked

I complained about this limitation on Connect, but it was quickly closed as Won't Fix.

Another problem with vanilla conversion to XML is that this will convert various entities into "safe" versions; for example, WHERE x > 10 will change to WHERE x > 10.

A better solution

It occurred to me that this would be yet another useful scenario for an ordered split function. By creating a table-valued function that splits a string on CR/LF, and returns each resulting sub-string in the original order, we could return all of the query - in either text or grid mode. First, let's create a Numbers table:

CREATE TABLE dbo.Numbers

For simplicity, I am going to limit the numbers (and hence the supported string length, in characters) to 1 million. You can make the Numbers table larger (and therefore support much longer strings) by adding an additional CROSS JOIN, which I've commented out, and changing the parameter to TOP.

;WITH n(n) AS 
  SELECT TOP (1000) number 
    FROM master.dbo.spt_values
    WHERE [type] = N'P' AND number BETWEEN 1 AND 1000
    ORDER BY number
INSERT dbo.Numbers(n)
  SELECT TOP (1000000) n = ROW_NUMBER() OVER (ORDER BY n1.n)

With 1,000,000 rows, this table is 11 MB (with page compression) and 13 MB without. Certainly tolerable, especially since this will only be pulled into memory when you're testing dynamic SQL strings, or if you find it useful for other string splitting problems too (personally, I find Numbers tables and split functions immensely valuable).

You may want to support longer strings; of course you would probably want no more than a billion rows in this table - not only will that be a waste most of the time, your strings can't possibly be any bigger than that anyway, given that NVARCHAR(MAX) is limited to 2 GB (about a billion Unicode characters).

As an aside, supporting a billion rows will require a much larger table, which will likely take a much longer time to populate initially, and which will likely incur a healthy dose of log and tempdb activity. Do not try this on a small VM with limited disk space and memory.

Now, the inline table-valued function, which you can put in a central utility database, or master, or wherever you think it can be useful:

ALTER FUNCTION [dbo].[SplitStrings_Ordered]
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
      SELECT n, s = SUBSTRING(@List,n,CHARINDEX(@Delimiter,@[email protected],n)-n)
        SELECT n FROM dbo.Numbers 
          WHERE n <= LEN(@List)
          AND SUBSTRING(@[email protected],n,LEN(@Delimiter)) = @Delimiter
      ) AS y

And finally, to present the output of the dynamic SQL command without worrying about truncation or XML entitization:

SELECT s FROM dbo.SplitStrings_Ordered(@sql, CHAR(13)+CHAR(10)) ORDER BY n;

The ORDER BY is important; it will ensure that each line gets returned in the correct sequence.

Next Steps

Last Updated: 2014-03-24

get scripts

next tip button

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Thursday, March 14, 2019 - 6:28:48 PM - Mateusz Nowak Back To Top

I have another solution for this problem.

You can use Save Result As ... to save data in a flat file (CSV or TXT).

Query in the file has full length.

Monday, March 24, 2014 - 4:34:37 PM - TimothyAWiseman Back To Top

Excellent post, thank you for sharing it.

I faced a similar situation a while ago and detailed a somewhat different response to it, though I was using print function rather than selecting.  I detailed my approach here.

Also, when using dynamic SQL if any of the paramaters are user supplied it is generally wise to take precautions against SQL injection.

Learn more about SQL Server tools