Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to list the source table name of columns in a VIEW (SQL Server 2005) Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 12:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:06 PM
Points: 3, Visits: 36
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
Post #1346355
Posted Friday, August 17, 2012 1:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1346358
Posted Friday, August 17, 2012 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:06 PM
Points: 3, Visits: 36
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
Post #1346378
Posted Friday, August 17, 2012 2:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1346388
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse