view and table relationship - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

view and table relationship

Author

Topic

safderalimd
Starting Member

32 Posts

Posted - 2008-11-12 : 13:29:13

I am looking for select statement against system tables/views to get following result set. Is it possible?

view_name
view_column_name
table_name
table_column_name

I want to link column in a view to column in base table. I can live ignoring derived columns in view that uses functions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 13:31:41

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

safderalimd
Starting Member

32 Posts

Posted - 2008-11-12 : 15:44:27

Here is example of what I am looking for

--Here is sample view definition
create view individual
select person.first_name as FirstName
,person.last_name as LastName
,address.street1 as Address1
,address.street2 as Address2
,city.name as City
From person
left join [address] on address.person_id = person.person_id
left join city on city.person_id = person.person_id

--I am looking for below result set
(row_id) (view_name) (view_column_name) (table_name) (table_col_name)
----------------------------------------------------------------------
1 individual FirstName person first_name
2 individual LastName person last_name
3 individual Address1 address street1
4 individual Address2 address street2
5 individual City city name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:45:13

[code]select row_number() over (order by column_name),view_name,table_name,column_name as table_column_name
from information_schema.view_column_usage
where view_name=your view name here'[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources