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

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?

CodePudding user response：

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 '
        , '''', 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);