total count and total distinct counts of records inside all dbo tables

Question

I want sql query which gives me output as total count and total distinct counts of records inside all tables which are present in my dbo schema along with specific table name.

Answer 1

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

Answer 2

SELECT s.name AS [Schema], o.name AS [Table], SUM(p.rows) AS rows
FROM   sys.schemas s
JOIN   sys.objects o ON s.schema_id = o.schema_id
JOIN   sys.partitions p ON o.object_id = p.object_id
WHERE  p.index_id IN (0, 1)
  AND  o.type = 'U'
GROUP BY s.name, o.name
ORDER  BY s.name, o.name

Answer 3

Please also check:

DECLARE @QueryString NVARCHAR(MAX) ;  
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')  
                      + 'SELECT '  
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))  
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]  
                      , COUNT(*) AS [RowCount] FROM '  
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))  
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '  
FROM sys.objects AS sOBJ  
WHERE  
      sOBJ.type = 'U'  
      AND sOBJ.is_ms_shipped = 0x0  
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;  
  
PRINT @QueryString  
EXEC sp_executesql @QueryString  
GO  

For the different number of rows in each table, it seems that there is no way to achieve it with one query.As ErlandSommarskog said, you may need to run a separate query for each table.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

DISTINCT Total records value as well.

DISTINCT over what? All columns? If your table(s) do have a primary key, the you get as DISTINT over all column the same value as a COUNT over all.