SQL Query to get table name and number of rows based on column name"

Question

Hello Everyone,

I have declared a Cursor to get table names and no of columns in that tables based on column names.Please find the below query table name is not get inserted.Please suggest.

Create table #t
(
tabname varchar(500),
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 
--Print @Namee
insert into #t(tabname) SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_NAME =' + @Namee + '
exec ('insert into #t(NoOfRows)  Select count(*) from ' + @Namee + ' where isactive=0')
Fetch Next From @Getname into @Namee
End
Close @GetName
Deallocate @GetName
select * from #t 

Answer 1

You can insert the table name and number of rows in a single INSERT:

EXEC('INSERT INTO #t
         (tabname, NoOfRows)  
      SELECT '''+ @Namee +''', COUNT(*) 
        FROM ' + @Namee + ' 
       WHERE isactive = 0')

What you have makes no link between the table name and the count, so it's unlikely you're missing a table but it is doubtful that the NoOfRows was actually associated with the table name in the record.

Answer 2

You are doing two inserts into your temporary table, one for the table name (With no count) and one for the count with no table name.

See OMG Ponies for the SQL to replace yours with and remove the insert with just a table name

Answer 3

Here is a better way to get the tables you want (won't have some issues with catalog and schema overlap)

declare @colname varchar(max)
set @colname = 'isactive'

SELECT table_name from information_schema.tables t
  join information_schema.columns c on t.table_catalog = c.table_catalog and    
                                       t.table_schema = c.table_schema and 
                                       t.table_name = c.table_name and 
                                       column_name = @colname