How to list the source table name of columns in a VIEW (SQL Server 2005)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » How to list the source table name of columns...

How to list the source table name of columns in a VIEW (SQL Server 2005)

Rate Topic

Display Mode

Topic Options

Author

Message

lpfy

Posted Friday, August 17, 2012 12:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 1:48 AM
Points: 3, Visits: 21

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

Phil Parkin

Posted Friday, August 17, 2012 1:02 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 4,233, Visits: 9,469

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1346358

lpfy

Posted Friday, August 17, 2012 1:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 1:48 AM
Points: 3, Visits: 21

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

Phil Parkin

Posted Friday, August 17, 2012 2:12 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 4,233, Visits: 9,469

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1346388

« Prev Topic | Next Topic »

Permissions