CODEWITHSUNDEEP
sqlsql-server
Simhadri
Simhadri

Reputation: 931

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

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

Upvotes: 2

Views: 1931

Answers (4)

tster
tster

Reputation: 18237

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'

Upvotes: 1

Preet Sangha
Preet Sangha

Reputation: 65496

Replace 

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

with 

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

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

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'

Upvotes: 2

Mike Forman
Mike Forman

Reputation: 4527

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'

Upvotes: 1

Related Questions