Table list view code??? (1 Viewer)

[Emmanuel]

Hi team,

I'm looking to get a code that I can use to list the fields inside a table. I am in the process of converting all our tables to views so that I can better maintain it for the users and give it more functionality without the users having to link to many tables. The problem is that some of our tables have hundreds of fields and I am not about to type them all one at a time.

Could you share with me if there is a command or a line of code that I can use to pull all the filed in a table? For example.

select "list fields" from table1


Thank you in advance,

webmanny.com

 

[MSAccessRookie]

Hi team,

I'm looking to get a code that I can use to list the fields inside a table. I am in the process of converting all our tables to views so that I can better maintain it for the users and give it more functionality without the users having to link to many tables. The problem is that some of our tables have hundreds of fields and I am not about to type them all one at a time.

Could you share with me if there is a command or a line of code that I can use to pull all the filed in a table? For example.

select "list fields" from table1

Thank you in advance,

webmanny.com

A Query similar to the following could give you close to what you want.

[LEFT][B]Select[/B] [COLOR=blue][B]Column_Name[/B][/COLOR] from [COLOR=green][B]INFORMATION_SCHEMA.COLUMNS[/B][/COLOR]
[B]Where[/B] [COLOR=blue][B]Table_Name[/B][/COLOR] = {[COLOR=red][B]Your Table Name[/B][/COLOR]}
[B]Order By[/B] [B][COLOR=#0000ff]Column_Name[/COLOR][/B] [/LEFT]

 

[Emmanuel]

Wow, that was quick. Unfortunatelly, it does not work. I tried runnign it and it didn't work. I get the following error.

"Error: Syntax error or access violation (State:42000, Native Code: 0)"

If it helps. I'm running on Sybase IQ. Thanks for your help so far.

 

[MSAccessRookie]

Wow, that was quick. Unfortunatelly, it does not work. I tried runnign it and it didn't work. I get the following error.

"Error: Syntax error or access violation (State:42000, Native Code: 0)"

If it helps. I'm running on Sybase IQ. Thanks for your help so far.

I indicated that "A Query similar to the following could give you close to what you want", because you had not indicated the SQL Server Type, and therefore I provided a solution that was appropriate for the Forum that you chose (MicroSoft SQL Server). Oracle has a similar method of doing the same thing, and I am sure that SyBase does as well, but I am not familiar with SyBase.

 

[Emmanuel]

Thank you for your help. Using your code and some other online sources, I found the right combination. For those using Sybase IQ, this is what the code looks like.

Select column_name
From sys.systable
inner join sys.syscolumn
on sys.systable.table_id = sys.syscolumn.table_id
and sys.systable.table_name = 'TABLE NAME'

order by sys.syscolumn.column_id