I want to find all of the columns in a database that have never been used. By that I mean that for numeric column types, they coalesce to zero and for string column types they coalesce to a zero length string.

I easily created a query that gives me every column name in every table using the data management views. I also easily created a query, that given a table name and a column name gives me a count of discrete values found in that column.

What I want to do is cross apply those two queries so that I get one result set with the table name, column name and count of distinct values. I could then filter that to just the columns that have 1 or two distinct values and have the list that I want.

The issue is how to get the to merge the table and column names from the results of the first query into the second query. Dynamic sql works but I can not put it inside of a table valued function that I could cross join to get my desired result set because dynamic sql would make the function non-deterministic if it were allowed.

Does anyone have a method to accomplish this task?