Looping through fields in a table 1

[webuser]

Is there any way to loop through all the fields in a table within say, a triiger or a stored procedure, the way it can be done in ADO. What I am trying to accomplish is similar to the following code in ADO:

for x = 0 to MyTable.Fields.Count
debug.print MyTable.Fields(x).value
Next x

I know that this might not be possible, but is there a workaround? I find myself in this situation quite often and end up using a list of field names, which can become impossible to manage...

Thanks in advance...

 

[vishnuprasad]

U have to use While condition.For tables u will have to use this with cursors

E.g

Declare @str varchar(30)
Declare curname cursor select field from table
open curname
fetch next from curname into @str
while @@fetch_status = 0
begin
print @str
fetch next from curname into @str
end
close curname
deallocate curname


Further instead of @@Fetch_status u can use @@Cursor_rows
See SQL BOL for further help

Hope this is ok

 

[webuser]

Thanks, but I don't think this accomplishes what I wanted to do. I need to loop through the FIELDS of a table, not the records - i know how to do that. Like I said, I want to go through every field in a specific record ore records. is there any way?

Thanks again.

 

[cmmrfrds]

The SQL Server tables are defined in the table sysobjects. The columns are defined in the table syscolumns.

something like:

select name from syscolumns, sysojects
where xtype = 'U' and name = 'yourtablename'

You will need to look in these 2 tables to get the correct names.

 

[vishnuprasad]

I think cmmrfrds is right for the soln u have asked for, but query can be as simple as follows :

select name from syscolumns
where id = object_id('table_name')