How to get the count of records in all tables of the database group by based on one single column using a single query approach.(Applicable to sql server)

 

Tried to write multiple queries into a single query with joins but none worked so ended up with below approach

SELECT 'select count(*), tenant_uuid from ' + TABLE_NAME + ' group by tenant_uuid' from (select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='tenant_uuid') ab

Also one analysis about row count :

Only one query which gives the correct row count of all tables  out of many available in the net in case of SQL server is below one:

SELECT Top 4 sysobjects.[name], max(sysindexes.[rows]) AS TableRows 

  FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id] 

WHERE sysobjects.xtype = 'U' 

GROUP BY sysobjects.[name] 

ORDER BY max(rows) DESC

Tried with cursor but it is giving some other error as pasted below

declare cnt_tables cursor fast_forward for

SELECT  tab.name AS 'Table_Name',col.name as 'column_name'

FROM sys.columns col

JOIN sys.tables  tab  ON col.object_id = tab.object_id

WHERE col.name = 'tenant_uuid' 

ORDER BY Table_Name

--select table_name from information_schema.tables

open cnt_tables

declare @tablename varchar(255)

declare @columnname varchar(255)

declare @stmt nvarchar(2000)

declare @rowcount int

fetch next from cnt_tables into @tablename,@columnname

while @@fetch_status = 0

begin

select @stmt = 'select @rowcount = count(*),tenant_uuid' + 'from ' + @tablename group by + @columnname

exec sp_executesql @stmt, N'@rowcount int output', @rowcount=@rowcount OUTPUT

print N'table: ' + @tablename + ' has ' + convert(nvarchar(1000),@rowcount) + ' rows'

fetch next from cnt_tables into @tablename

end

close cnt_tables

deallocate cnt_tables

Each GROUP BY expression must contain at least one column that is not an outer reference.