How to use EXEC or sp_executeSQL without a loop in this case?

Question

Environment: SQL Server 2005/2008, pubs database

I inserted a dataset variable into the table as shown below using the information_schema tables.

Now I would like to update the flag column based on the result of the query in the dSQL column. I managed to update the use of loops / cursor, and then use sp_executeSQL to update the column and then update the flag column later. But is there an alternative dial-based method without a loop across all the individual lines?

use pubs go declare @dsql Nvarchar(max)='', @tablename varchar(100), @colname varchar(100) declare @t table ( TABLE_NAME varchar(100), COLUMN_NAME varchar(100) ) insert into @t select distinct t.TABLE_NAME, c.COLUMN_NAME from information_Schema.tables t inner join information_Schema.columns c on t.TABLE_CATALOG = c.TABLE_CATALOG where t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_TYPE = 'BASE TABLE' and c.DATA_TYPE = 'varchar' select *, Dsql = 'select ' + COLUMN_NAME + ' from ' + TABLE_NAME + ' WHERE ' + COLUMN_NAME + ' = ''Menlo Park''', '' as Flag FROM @t GO 

I had the idea of ​​creating a function and calling a function for each line to execute a separate query statement, but calling a function for each record can be a performance hit.

Answer 1

Impossible, I made the script as before.

 declare @searchvalue varchar(100) set nocount off set @searchvalue = 'Hello world' create table #tt (table_name varchar(64), column_name varchar(64), count int) select * into #t from ( select 'select ''' + a.table_name + ''' ''table_name'',''' + a.column_name + ''' ''column_name'', count(*) count from [' + a.table_name +'] where [' +a.column_name+']=''' +@searchvalue +'''' + ' group by ['+ a.column_name+']' sqlstring from INFORMATION_SCHEMA.COLUMNS a join INFORMATION_SCHEMA.TABLES b on a.table_name = b.table_name and b.table_type = 'base table' where data_type = 'varchar' ) a --loop cursor Declare @sqlstring as nvarchar(500) Declare SqlCursor CURSOR FAST_FORWARD FOR SELECT sqlstring FROM #t OPEN SqlCursor FETCH NEXT FROM SqlCursor INTO @sqlstring WHILE @@FETCH_STATUS = 0 BEGIN insert #tt exec(@sqlstring) FETCH NEXT FROM SqlCursor INTO @sqlstring END CLOSE SqlCursor DEALLOCATE SqlCursor select * from #tt drop table #tt drop table #t 

Use what you want

Answer 2

This is the loop or function that you suggested (which is a loop anyway).

Answer 3

This is an old question, but I still want to add another answer.

Try to execute the script (no cursor, no loop (according to the execution plan)): (verified in MS SQL 2012)

 -- Setting up test data/code SELECT N'SELECT * FROM INFORMATION_SCHEMA.COLUMNS AS C' T INTO #Code UNION ALL SELECT N'SELECT * FROM INFORMATION_SCHEMA.TABLES AS T' -- Variable to hold the selected queries, seperated by CrLf. You can also add a "GO" or ";" DECLARE @SQL NVARCHAR(MAX) = CHAR(13) + CHAR(10) -- Concatenate the selected queries together into the variable SELECT @SQL = @SQL + CHAR(13) + CHAR(10) + CT FROM #Code AS C -- Execute EXEC sys.sp_executesql @SQL -- Clean up DROP TABLE #Code