attribute name of 2nd column 1

[russland]

hi,

I loop though a string list of database tables. Now since I've got the tables it should be easy to find out the 2nd attribute name of the table respectively the name of the 2nd column. Any clue?

Thanks a lot in advance.

 

[JarlH]

Exactly what are you asking for???

Column names are found in INFORMATION_SCHEMA.COLUMNS.

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YOURTABLE' -- Replace YOURTABLE
AND ORDINAL_POSITION = 2; -- 2 is for second column

But is this really what you want to know...? What do you mean with "2nd attribute name"?

 

[russland]

Sorry for that. I'm asking for the column name to find out.

...and I'm using MS SQL Server 2000. (Thats probably the reason why I couldn't find INFORMATION_SCHEMA (is that Oracle?))

thanks so far.

 

[PHV]

is that Oracle?
As you asked in the ANSI SQL forum you've got an ANSI answer.

 

[russland]

Yes, and I didn't complain.

 

[russland]

okay,

here is something very appropriate to receive the attribute's name using ms sql server 2000.

SELECT syscolumns.name
FROM syscolumns
INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
WHERE (sysobjects.name LIKE 'C_%') AND (syscolumns.colid = 2)

 

[r937]

Information Schema Views
Microsoft® SQL Server™ 2000 provides two methods for obtaining meta data: system stored procedures or information schema views.

Note To obtain meta data, use system stored procedures, system functions, or these system-supplied views only. Querying the system tables directly may not provide accurate information if system tables are changed in future releases. [emphasis added]

so INFORMATION_SCHEMA views, which are standard, are supported in 2000, and microsoft recommends using those views

and i think i heard the system tables are being dropped in 2005



r937.com | rudy.ca