As a string column, I've constructed the query that I want to run. How can I execute it?

Question

All of my tables in the DIM schema share a bit column called USER_CHECKED. With an agent job, I want to output (I don't particularly care where/what to) the name of each table in the schema and the number of times USER_CHECKED has the value 0. Before I can write this agent job, I need to write the underlying query. The natural solution is to use sp_MSforeachtable, but that's unsupported. Instead, I've done something like this

USE [MY-DB]
SELECT
  TABLE_CATALOGUE,
  CONCAT('SELECT COUNT(CASE WHEN [USER_CHECKED] = 0 THEN 1 END) FROM MY-DB.DIM.', 'TABLE_CATALOGUE') AS TO_RUN
FROM
  information_schema.tables
WHERE
  TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA = 'DIM'

As a result, the TO_RUN column contains exactly what query I want to run. However, I've got no idea how to run it. In principle, I could use a cursor, but they're usually a terrible idea. Is there an alternative way to "execute" this column?

*How can I execute it?* - Maybe look at [Execute()](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver16)? What part of your query takes care of the `has the value 0`? — Stu, May 30 '22 at 18:22
@Stu I had that in mind, but I'm under the impression that they'd need to be procedures before that would work. — J. Mini, May 30 '22 at 18:24
@Stu Sorry about the '0' part. Will correct. Tried to be too smart with bits. As for the impression, every tutorial that I saw for the topic of "execute the contents of each row" referred to executing *procedures* even when I specifically searched for executing *queries*. — J. Mini, May 30 '22 at 18:28
It looks like you're half way there, you need to construct your dynamic SQL in its entirety to include the table name and [probably] insert the results into another table which you can later query. — Stu, May 30 '22 at 18:30
@Stu That's trivial. Just move a bit of the `SELECT` into the dynamic SQL and maybe `INSERT` In to a `'#temptable`. The only thing that stops me is my lack of knowledge on how to execute this query once for each row. I'll write the `WHILE` loop if I need to, but any pattern that avoids loops and cursors in SQL is a good one. — J. Mini, May 30 '22 at 18:33

score 2 · Accepted Answer · edited May 31 '22 at 00:16

One way you can accomplish this is to build a single string to execute with a combination of concat and string_agg to build a single query that unions your individual selects for each table.

You could then optionally include an insert into to have the results inserted somewhere.

It's preferable to use the system tables over the information schema views.

The basic idea which you will probably want to tweak is as follows - obviously untested for your environment:

declare @Sql nvarchar(max);

select @Sql = String_Agg(Convert(varchar(max),
    Concat(
        'select '
        , QuoteName(t.[name], '''')
        , ' as SourceTable, count(case when USER_CHECKED = 0 then 1 end) User_CheckedIsZero from ' 
        , QuoteName(t.[name])
    )), ' union all ' + char(13)) -- Includes return so print output is readable
from sys.tables t
where Schema_Name(t.schema_id) = 'DIM' and t.[type] = 'U' and t.is_ms_shipped = 0
    and exists (select * from sys.columns c where c.object_id = t.object_id and c.[name]='USER_CHECKED');


print @Sql;
-- exec(@Sql);

As a string column, I've constructed the query that I want to run. How can I execute it?

1 Answers1