Finding column name associated with view

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

General SQL Server Forums

New to SQL Server Programming

Finding column name associated with view

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2007-04-30 : 16:49:40

I want to find columns name associated with view. For example; can you please help me to write query? I need to write for several tables.

TableName Column View
---------- -------- -------
Employee EmpId v_EmployeeDetails
Employee Firstname v_EmployeeDetails
Employee Lastname --
Employee SSN v_PersonalD
Employee DOB --

___________________________________________________
--just example

go
create view v_EmployeeDetails
as
select EmpId,Firstname from Employee

go

go
create view v_PersonalD
as
select SSN from Employee

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-30 : 17:05:02

Are you trying to create a view for each column in the table?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sanjnep
Posting Yak Master

191 Posts

Posted - 2007-04-30 : 17:10:32

No this is just an examlpe. I want to write a query which will find view's name associated with columns.

like:

select object_name (id),name as 'column name'--, (I need to find view's name)'
from syscolumns where id = object_id ('nt_sis_trannum')

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-30 : 17:50:19

Something like this ?


select [View] = object_name(sc.id), sc.name 
from syscolumns sc
join sysobjects so on so.id = sc.id
where so.type = 'v'
order by object_name(sc.id) , sc.name

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-01 : 10:09:42

Or kust query information_schema.view_column_usage.

sanjnep
Posting Yak Master

191 Posts

Posted - 2007-05-01 : 16:23:49

I am able to do that.
Now how can I find the column name in the view which is alredy in another view?

for example:

create view v_parent
as
select EmpId,Firstname from Employee
---------------------------------------------------
create view v_child
as
select EmpId from v_parent
------------------------------------------------------

I want the following result:

TableName Column View1 View2
---------- -------- --------- ----------
Employee EmpId v_parent v_child
Employee Firstname v_parent -----
Employee Lastname -- ---
Employee SSN -- ---
Employee DOB -- ---

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources