Hi Joe,
i've tried to do this using temp tables as i thought they were an alternative to them..
I've got a list of columns, and i've retrieved the table names which they exist in using a cursor. Now i need to know how many columns exist in each table and ideall ythe name of the columns aswell. I'm nearly there, but not quite, can you help me complete the code..
here's my code, so far:
-- cursor
print 'Table Iterator Starting'
go
declare my_csr cursor for
select name, id
from sysobjects
where id in ( select distinct id from syscolumns
where name in ( 'Loan_Prps_Cde', 'YTD_DEL_COMP', 'SD_MTM', 'Disc_Amt', 'INT_RECV_PERF_CRDT', 'INT_RECV_PERF_DBT', 'INT_RECV_PERF_ALL', 'SD_MV', 'MV_To_Post', 'TD_Funded', 'TD_MTM', 'Other_TO_Post', 'FEE_RECV_PERF_ALL', 'FEE_RECV_PERF_DBT', 'FEE_RECV_PERF_CRDT',
'Other_Rec_TO_Post', 'TD_Disc', 'SD_Loan_Deposit', 'MTM_Pend_Amt', 'Disc_Pend_Amt', 'Funded_Pend_Amt'))
go
begin
declare @name varchar(20),@id int
declare @counter int
open my_csr
fetch my_csr into @name, @id
while @@sqlstatus = 0
begin
print 'investigating table %1!', @name
-- looping over a given table i want to keep a count of how many of the columns above exist in the table
--- what should my sql be here???
fetch my_csr into @name, @id
end
end
close my_csr
deallocate cursor my_csr
Main Topics
Browse All Topics
by: Joe_WoodhousePosted on 2005-11-17 at 04:24:54ID: 15310786
WHILE loops in Sybase T-SQL don't work like this. (I'm assuming you're using Sybase ASE.) The WHILE expression must be a test that evaluated to true or false.
The type of loop you want is usually done with a cursor, because you can build a WHILE loop test for "are there any more rows to fetch?" Cursors are well documented in the ASE Reference Manual - look up "declare cursor" at:
http://sybooks.sybase.com/
Briefly your code becomes:
declare @table_name varchar(20)
declare name_cursor cursor
for SELECT name FROM tempdb..petergib_tu
for read only
open name_cursor
fetch name_cursor in @table_name
while @@sqlstatus = 2 -- ie no errors and we successfully fetched a row
begin
exec sp_spaceused @table_name
fetch name_cursor in @table_name
end
close name_cursor
deallocate cursor name_cursor
(This syntax will only work inside a procedure...)