Stored Procedure looks at sysobjects and process info in each table

[zseifts]

Hi everyone.

I am fairly new to using stored procedures but for the past few days I've been reading up on them and I need to do the following:

Get all the table names from the sysobjects table
Take all of those names and use them as the table name in the SELECT command
Process each table individually.

I also need to be able to either output a view or have the stored procedure output all the rows that it has processed.

I've done some reading on using cursor and I think thats the way I want to go but I'm really not sure where to go. If anyone has any ideas on how to do this or could just point me in the right direction, it would be greatly appreciated.

Thanks.

[aaryan]

hi zseifts

declare @name varchar(20)
declare sampcursor1 cursor for select name from sysobjects where type = 'U' and sysstat <> 8275
open sampcursor1
fetch next from sampcursor1 into @name
while @@fetch_status = 0
begin
declare @sql varchar(8000),@from varchar(10)
set @from = @name
select @sql = 'select * from ' + @from
exec(@sql)
fetch next from sampcursor1 into @name
end
close sampcursor1
deallocate sampcursor1
go

hope this will help u to proceed further

[iburyak]

You did a grate job for a novice.

Your variable name @from is 10 characters long and @name is 20 it cuts names and system doesn't recognize tables.

1. Make them equal @from varchar(20)
2. You don't need @from at all you can use @name instead.
3. Check if you have tables with longer names than 20 in sysobjects and be generous declare @name varchar(100).... :)


Good luck.

[zseifts]

Thanks aaryan and iburyak, I'm going try this when I get my test sql server up.

[zseifts]

Okay, So I've tried to get that SP working that was posted earlier, but its not working. I can get it to loop through once then it would return the info from the first table and none other ones. Anyone have any ideas?

-Zach

[iburyak]

How do you get the data?
If you run through something like SQL Query analyzer you should be able to see everything.
If through some front end like VB you should request next recordset in a loop to be able to see more.

Did you change size of @from varchar(10) to @from varchar(20).
Run
select name from sysobjects where type = 'U' and sysstat <> 8275
and see how many records you should have in return.