SQL Server – Pass Query Result as Argument to EXEC sp_executesql

sql serversql-server-2012

I blatantly stole from the answer here to make this : (this is not the only case I am looking a solution for)

SELECT   'SELECT Top 10 [table] = ''' 
         + REPLACE(name, '''','') 
         + ''', * FROM ' 
         + QUOTENAME(SCHEMA_NAME([schema_id]))  
         + '.' 
         + QUOTENAME(name) 
         + ';' as SQLText
FROM     sys.tables AS t
ORDER BY name;

The reason was that in the answer the DECLARE @sql NVARCHAR(MAX) was not enough to hold all the tables names, I think we have 120 tables, (or whatever), and the SQL text was being clipped at NVARCHAR(MAX) limit.

Maybe in this case can EXEC sp_executesql SQLText and unionizing them,
but is there a way to turn all the rows into one text and execute that instead?

Is there a limit on the size of how big text of a stored procedure can be?

Please note, I am not after the answer for this specific case, but this is an example of type of problem I am trying to solve.

Best Answer

It wasn't problem with NVARCHAR(MAX) limit for sure. 120 tables isn't a lot and NVARCHAR(MAX) limit is a way above what is needed here (up to 2 GB data), more information link.

I've just checked this solution and it works for me (399 tables).

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql += 'SELECT Top 1 [table] = ''' 
            + REPLACE(name, '''','') 
            + ''', 1 FROM ' 
            + QUOTENAME(SCHEMA_NAME([schema_id]))  
            + '.' 
            + QUOTENAME(name) + ';' 
FROM     sys.tables AS t
ORDER BY name;

EXEC sp_executesql @sql

If you want to see the whole content of NVARCHAR(MAX) value in SSMS (SELECT and PRINT have limits), you could do something like that:

SELECT @sql AS [processing-instruction(x)]
FOR XML PATH('')

As suggested by @Dan Guzman, more reliable approach would be:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = STUFF((SELECT 'SELECT Top 1 [table] = ''' 
                            + REPLACE(name, '''','') 
                            + ''', 1 FROM ' 
                            + QUOTENAME(SCHEMA_NAME([schema_id]))  
                            + '.' 
                            + QUOTENAME(name) + ';' 
                     FROM sys.tables AS t
                     FOR XML PATH(''), TYPE).value(N'.[1]', N'varchar(max)'), 1, 0, '');

EXEC sp_executesql @sql
Related Question