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.
