How to count, for every table, distinct values in a column that is shared by an unspecified number of tables in SQL Server?

Question

I have a fluid and indeterminate number of views in a SQL Server database with a column called "CONFIDENCE". For every view in that set, I would like to count how often distinct values turn up in that column.

I know how to find all views that have the "CONFIDENCE" column...

select  c.table_schema as schema_name,
        c.table_name as name
  from  information_schema.columns c
  join  information_schema.tables t
    on  c.table_name = t.table_name
 where  c.column_name = 'confidence' 
        and t.table_type = 'view'

And I know how to count distinct values of a single view...

  select distinct confidence,
         count(*) as occurrences
    from schema.view_name
group by confidence
order by confidence;

But I don't know how to connect the two. I suspect this has to do with cross apply or union all, but I have only been able to find resources for union-ing a fixed number of objects.

Answer 1

You need to know the answer to one query to write the other, you can solve this with a little DSQL.

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL = @SQL + 'UNION ALL SELECT ''' + c.table_schema + '.' + c.table_name + ''' tbl, confidence, count(*) as occurrences FROM [' + c.table_schema + '].[' + c.table_name + '] GROUP BY confidence
'
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_name = t.table_name
WHERE c.column_name = 'confidence' 
    AND t.table_type = 'view'

SET @SQL = RIGHT(@SQL, LEN(@SQL) - 9)
EXEC sp_executesql @SQL