February 19, 2009 at 12:38 pm
Hi There,
The Information_schema holds usefull information about for example table- and column names.
Select TABLE_NAME, COLUMN_NAME
FROM MyDataBase.Information_schema.Columns
But how do I select all column names after column two in each table?
Regards Joejoe
February 19, 2009 at 12:42 pm
If you drag the "Columns" folder from each onto the workspace, it will produce a list of the columns. Remove the ones you don't want, and you'll have what you do want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2009 at 12:53 pm
Hi,
Use the row_number function to create a row number for each column. The partition clause means that the count will restart every time table_name changes.
select TABLE_NAME, COLUMN_NAME
from (Select TABLE_NAME, COLUMN_NAME, row_number() over(partition by table_name order by column_name) as row_num
FROM Information_schema.Columns) x
WHERE row_num > 2
Bevan
February 19, 2009 at 12:54 pm
I have no idea why you would want to do this programmatically, but you could do something like this:
SELECT TABLE_NAME, COLUMN_NAME
FROM MyDatabase.Information_schema.Columns
WHERE ORDINAL_POSITION > 2
ORDER BY TABLE_NAME, ORDINAL_POSITION
Greg
February 20, 2009 at 3:58 am
Hi There,
Thanks for you taking your time to reply.
Now I have what I need.
Best Regards
Joe joe
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply