Try
Declare @TableName sysname, @ColumnNames varchar(max);
Create Table #Result ([dbo Table] sysname, [Count] int, [Distinct Count] int);
Declare @Sql nvarchar(max);
Declare Cur Cursor Local Static Read_Only For
Select t.name, String_Agg('['+c.name+']', ',') As ColumnNames
From sys.tables t
Inner Join sys.columns c On t.object_id = c.object_id
Where SCHEMA_NAME(t.schema_id) = 'dbo'
Group By t.name;
Open Cur;
Fetch Cur Into @TableName, @ColumnNames;
While @@FETCH_STATUS = 0
Begin
Set @Sql = '
With cte As
(Select ' + @ColumnNames + ', Count(*) As GroupCount
From ['+ @TableName + ']
Group By ' + @ColumnNames + ')
Insert #Result([dbo Table], [Count], [Distinct Count])
Select ''' + @TableName + ''', Sum(GroupCount) As Count, Count(*) As DistinctCount
From cte';
-- Select @TableName, @ColumnNames;
-- select @Sql;
exec sp_executesql @sql;
Fetch Cur Into @TableName, @ColumnNames;
End;
Close Cur;
Deallocate Cur;
Select * From #Result;
Tom