Iterate over table rows containing TSQL queries and exceute the results

Question

Hello All,
I have a scenario wherein SQL queries are present as rows within a table as seen below:

We need to iterate across all rows and execute it as dynamic SQL like
EXEC(@row1 output)
and get all results.

And in a 2nd use case, get the list of table names that have count(*)>5.

So its a case of getting all outputs and also getting filtered output based on some filtered scenario of output results

Answer 1

You can capture the result from the dynamic SQL with INSERT-EXEC:

   INSERT SomeTable(value)  
      EXEC (@row1)  

But you would only have the counts. To get the table names into the result you would have to parse the query to get the table name. It would be easier to generate the queries so that the table names inside the queries.

.Then again, if you want to get a list of all tables in the database with a rowcount > 5, you can use this query:

   SELECT s.name, o.name, SUM(p.rows) AS rows  
   FROM   sys.schemas s  
   JOIN   sys.objects o ON s.schema_id = o.schema_id  
   JOIN   sys.partitions p ON o.object_id = p.object_id  
   WHERE  p.index_id IN (0, 1)  
     AND  p.rows > 5  
   GROUP BY s.name, o.name  
   ORDER BY s.name, o.name  

Answer 2

Hi @Nandan Hegde
Check this sample:

CREATE TABLE #Dynamic_List (Dynamic varchar(max), OrderBy INT IDENTITY(1,1))  
INSERT INTO #Dynamic_List VALUES  
('SELECT COUNT(*) FROM Table1'),  
('SELECT COUNT(*) FROM Table2'),  
('SELECT COUNT(*) FROM Table3');  
  
--Create Result Table To Save Records  
CREATE TABLE #TempResult (Dynamic_Code varchar(max), Count_Result INT)  
  
DECLARE @sqlcmd VARCHAR(MAX);  
SELECT @sqlcmd = STUFF(( SELECT ';'+char(10)+ 'INSERT INTO #TempResult SELECT '''+Dynamic+''','+RIGHT(Dynamic,LEN(Dynamic)-6)+char(10)   
                         FROM #Dynamic_List order by [OrderBy] FOR XML PATH('') ), 1, 1, '')  
EXEC(@sqlcmd);  
  
SELECT * FROM #TempResult  
  
SELECT *,RIGHT(Dynamic_Code,LEN(REPLACE(Dynamic_Code,'FROM','%'))-CHARINDEX('%',REPLACE(Dynamic_Code,'FROM','%'),1)) AS Table_Name  
FROM #TempResult  
WHERE Count_Result > 5  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.