View Column to Actual Table Columns

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 2000 Forums

SQL Server Development (2000)

View Column to Actual Table Columns

Author

Topic

subrahmanyam
Starting Member

4 Posts

Posted - 2005-03-19 : 14:33:20

Hi,
Can any one help me how to compare the view column names with the actual table column names where i should be able to view the view column name against to the actual table column name through a query.
in query it should compare the table name and view name.

Thanks,
lhcl

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-19 : 14:48:38

One way:
exec sp_helptext <viewName>

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-19 : 15:14:25

Add to the thread rather than editing your orginal post. It's confusing for the readers.

I don't know of a way to do that. Keep in mind that a view column can be a calculations between different columns in different tables. What are you trying to do?

Be One with the Optimizer
TG

subrahmanyam
Starting Member

4 Posts

Posted - 2005-03-19 : 15:44:45

quote:
Originally posted by TG

Add to the thread rather than editing your orginal post. It's confusing for the readers.

I don't know of a way to do that. Keep in mind that a view column can be a calculations between different columns in different tables. What are you trying to do?

Be One with the Optimizer
TG

example:
Create table table1 (id int, name varchar(30))
Create view vw1 as
select id as orderid , name as ordername from table1

now how will i get for a table1.id is vw1.orderid and table1.name is vw1.ordername ?

even i tried with the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE table in the sql server but there also i couldn't find any solution.

Thanks,
lhcl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-21 : 04:30:18

quote:
Originally posted by TG

One way:
exec sp_helptext <viewName>

Be One with the Optimizer
TG

As TG pointed out, you can see the column relations by seeing the query you used

Madhivanan

Failing to plan is Planning to fail

subrahmanyam
Starting Member

4 Posts

Posted - 2005-03-21 : 09:36:18

actually i need the comparision, which should be in a meta data style like how we will c the sysobjects the same way for a given view name and actual table name it should show the relation of actual column to view column name.

lhcl

quote:
Originally posted by madhivanan

quote:
Originally posted by TG

One way:
exec sp_helptext <viewName>

Be One with the Optimizer
TG

As TG pointed out, you can see the column relations by seeing the query you used

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-21 : 10:14:21

Don't know how many ways to say it. This isn't available "out of the box".
What would you like to see as your meta data "table" column value for these three
view columns?


create View MonthlyStats
as
Select	avg((InvoiceTotal - isNull(Payments,0))) as AvgOutstandingBalance
	,max((InvoiceTotal - isNull(Payments,0))) as MaxOutstandingBalance
	,count(*) as OutstandingBalanceCount

From	
	(
	Select	CustID,
		sum(TransAmount) as InvoiceTotal
	From	TransactionDetail
	Where	TransTypeID IN (1,3,5)
	AND	Status IN (1,8)
	Group by CustID
	) as inv

Left JOIN
	(
	Select	CustID,
		sum(TransAmount) as Payments
	From	TransactionDetail
	Where	TransTypeID IN (2,4,6)
	AND	Status IN (1,8)
	Group by CustID
	) as pay
		ON inv.custID = pay.custID
GO

Be One with the Optimizer
TG

subrahmanyam
Starting Member

4 Posts

Posted - 2005-03-24 : 15:09:12

Thank you for the support. but in my views actually i am not using any aggrigation funcations nor unions those are simple views with the column name differences.. here i am maintaining the database objects in such a way that, all the master tables data i am storing in a single table by differentiating with "type" value. Actual table has the columns like "shorttext1, shorttext2" etc.. for each master table instead of calling "shorttext1" i am using some allias names. So here i need a help of to identify view column name with the actual table name, not using "sp_helptext" .

quote:

Originally posted by TG

Don't know how many ways to say it. This isn't available "out of the box".
What would you like to see as your meta data "table" column value for these three
view columns?


create View MonthlyStats
as
Select	avg((InvoiceTotal - isNull(Payments,0))) as AvgOutstandingBalance
	,max((InvoiceTotal - isNull(Payments,0))) as MaxOutstandingBalance
	,count(*) as OutstandingBalanceCount

From	
	(
	Select	CustID,
		sum(TransAmount) as InvoiceTotal
	From	TransactionDetail
	Where	TransTypeID IN (1,3,5)
	AND	Status IN (1,8)
	Group by CustID
	) as inv

Left JOIN
	(
	Select	CustID,
		sum(TransAmount) as Payments
	From	TransactionDetail
	Where	TransTypeID IN (2,4,6)
	AND	Status IN (1,8)
	Group by CustID
	) as pay
		ON inv.custID = pay.custID
GO

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-24 : 15:51:49

PLEASE, someone correct or confirm what I'm saying.

There is NO way to query that information out of Sql Server. If you want that mapping in a query-able form, you'll need to build the table yourself (manualy) based on the code that was used to create the views.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources