Select Table values Through Cursor

Question

Hi,

Below is the cursor used to select values from tables in database

declare @table sysname
declare @tab varchar(max)
declare CT cursor
for
select table_name from INFORMATION_SCHEMA.TABLES
open CT
fetch next from CT into @tab
while @@FETCH_STATUS=0
begin
select  @tab
SET @table=@tab
select * from @table /* getting Error Here as please declare @table variable*/
fetch next from CT into @tab
end
close CT
deallocate CT

Please put some light and tell me where me going wrong for the same.

 

Answer 1

hi,

@table is not a table type variable, so you cannot issue

SELECT * FROM @table

J.

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

declare @table table
declare @tab varchar(max)
declare CT cursor
for
select table_name from INFORMATION_SCHEMA.TABLES
open CT
fetch next from CT into @tab
while @@FETCH_STATUS=0
begin
select  @tab
SET @table=@tab
select * from @table /* getting Error Here as please declare @table variable*/
fetch next from CT into @tab
end
close CT
deallocate CT

Hi Janos,

Thanks for the prompt response

I have declare the variable @table as  table but still gettin error

 

---

Harsimranjeet Singh

Answer 3

hi,

try this way:

declare @table as table (tablename sysname);
declare @tab varchar(max)
declare CT cursor
for
select table_name from INFORMATION_SCHEMA.TABLES
open CT
fetch next from CT into @tab
while @@FETCH_STATUS=0
begin
insert into @table values (@tab);

fetch next from CT into @tab
end
close CT
deallocate CT

select * from @table

 

I would not recommend to use cursor at all. Please try to write set based queries.

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 4

Jason,

I think it will only print out top 1 table entry in that table name @table

I want to select values from tables in query result pane...

I edited the same and i am getting the desired result

declare @table as table (tablename sysname)
declare @tab varchar(max)
declare CT cursor
for
select table_name from INFORMATION_SCHEMA.TABLES
open CT
fetch next from CT into @tab
while @@FETCH_STATUS=0
begin
insert into @table values (@tab);
exec('select top 1 * from '+ @tab)
fetch next from CT into @tab
end
close CT
deallocate CT

Thanks Jason for your help :)

Harsimran

---

Harsimranjeet Singh