August 17, 2012 at 12:54 am
Just wondering how to list column name and table name in one query for a view
for example
a view named as viewC, create by tbl1 inner join tbl2, contain a,b,c,d, 4columns.
a,b from tbl1, c,d from tbl2
How to Select COLUMN_NAME, DATA_TYPE, column_default, character_maximum_length, sourceTableNAME FROM information_schema.columns where table_name='viewC' together?
the result I want is:
column_NAME, table_name
a,tbl1
b,tbl1
c,tbl2
d,tbl2
Thanks
August 17, 2012 at 1:02 am
Have a look in INFORMATION_SCHEMA.VIEW_COLUMN_USAGE for the view-related info - you may have to join that to another i_s view to get everything you want.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 17, 2012 at 1:47 am
Thanks,
I have wrote my query and it works
SELECT INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE INNER JOIN
INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.VIEW_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
August 17, 2012 at 2:12 am
Nice work - thanks for posting back.
May I suggest that you consider using table aliases - it will make your query much more readable. Eg
select t.col1, t.col2 from tablewithaverylongnameindeedohyes t
--rather than
select tablewithaverylongnameindeedohyes.col1, tablewithaverylongnameindeedohyes.col2 from tablewithaverylongnameindeedohyes
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply