0

In One DB I have 100+ tables, but I need staging table (start with STR_) wise column wise(Which is Status ) row count.

Example:

Salect 'Table_name1' as Table, Count(*) as Rowcount from Table_name1 where user_status='Active' 
Union All
Salect 'Table_name2' as Table, Count(*) as Rowcount from Table_name2 where user_status='Active' 
union All
Salect 'Table_name3' as Table, Count(*) as Rowcount from Table_name3 where user_status='Active' 
Union All
Salect 'Table_name4' as Table, Count(*) as Rowcount from Table_name4 where user_status='Active'

I tried as below but I need in Dynamically

Salect 'Table_name1' as Table, Count(*) as Rowcount from Table_name1 where user_status='Active' 
Union All
Salect 'Table_name2' as Table, Count(*) as Rowcount from Table_name2 where user_status='Active' 
union All
Salect 'Table_name3' as Table, Count(*) as Rowcount from Table_name3 where user_status='Active' 
Union All
Salect 'Table_name4' as Table, Count(*) as Rowcount from Table_name4 where user_status='Active'
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Kittu SD
  • 11
  • 2
  • The question is unclear (what do staging tables have to do with row counts?), and the oldest SQL Server version in mainstream support is SQL Server 2019. 2008 is way too old. What are you trying to do, and what are those tables? Perhaps table partitioning is a better design? – Panagiotis Kanavos Feb 15 '23 at 14:05
  • You can use the undocumented [sp_msforeachtable](https://www.sqlshack.com/an-introduction-to-sp_msforeachtable-run-commands-iteratively-through-all-tables-in-a-database/) command to execute a query against every table in a database. The example in the link calculates the size of all tables . In your case you could use `SELECT ''?'' TableName, COUNT(1) Rowcount FROM ? WHERE status=''Active'''` – Panagiotis Kanavos Feb 15 '23 at 14:09

0 Answers0