Query to find row count of tables based on column name?

Question

Hello Everybody,

I have been trying to find out total count of rows in each tables which are de-activated. In my DB I have 20 tables with column IsActive.

I have tried below cursor,however it is getting error saying Invalid object name @Namee.

Create table #t
(
NoOfRows bigint,
)

Declare @Namee Varchar(500)
Declare @GetName Cursor
Set     @Getname = Cursor for 
Select table_name from information_Schema.columns
where column_name='isactive'Open @Getname
Fetch Next From @Getname into @Namee
While @@Fetch_Status=0
Begin 
insert into #t Select count(*) from @Namee where isactive=0
Fetch Next From @Getname into @Namee
End
Close @GetName
Deallocate @GetName
select * from #t 

Answer 1

Based on your example query, this should work. It will also be much faster than using the cursor

select
    OBJECT_NAME(c.object_id) as table_name
,   p.row_count
from sys.columns c
 join sys.dm_db_partition_stats p
    on c.object_id = p.object_id
    and p.index_id < 2
where c.name = 'IsActive'

Answer 2

Try this:

exec sp_msforeachtable '
if exists(
select * from sys.tables as t
join sys.columns as c on t.object_id = c.object_id
where t.name = ''?'' and c.name = ''IsActive'')

select count(*) 
from [?] 
where isactive=0'

Answer 3

Replace

insert into #t Select count(*) from @Namee where isactive=0

with

 exec ('insert into #t Select count(*) from ' + @Namee + ' where isactive=0')

Answer 4

While I haven't tested them, I would go with either Mike's or Denis's answer.

However, the problem here is the line:

insert into #t Select count(*) from @Namee where isactive=0

you are using @Namee like it is a table, but it is really a string with a table name. You would have to build the SQL dynamically and then exec it:

exec 'insert into #t select count(*) from ' + @Namee + ' where isactive=0'