Want to Retrieve information of specific tables where table count is greater than zero

Question

declare @table varchar(max)
declare @num int
declare cur cursor
for select table_name from information_schema.tables where table_name like '%lku'
open cur
fetch next from cur into @table
while @@FETCH_STATUS=0
begin

Set @num = exec('select count(*) from  '+@table) /* getting here error */

fetch next from cur into @table
end
close cur
deallocate cur

---

Harsimranjeet Singh

Answer 1

Hi Harsimranjeet,

Change the query like below:

declare @table varchar(max)
declare cur cursor
for select table_name from information_schema.tables where table_name like '%lku'
open cur
fetch next from cur into @table
while @@FETCH_STATUS=0
begin
exec ('select count(*) from  '+@table)
fetch next from cur into @table
end
close cur
deallocate cur

the above query should be able to retrieve the correct information that you are after.

Regards,
Jerry