Please start any new threads on our new site at 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)
 How to get a list of fields with source tables and ordinal positions for a VIEW

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2001-07-12 : 09:00:24
Mike writes "Hi, I'm a c++ programmer who has been sporadically using SQL server for under a year with no training other than a basic grounding in how select queries work and guerilla style reading to fill in the blanks on a need to know basis.

Let me start by saying that I have searched your FAQ and the site in general and didn't find anything obvious that could help me, sorry if I turn out to be an idiot.

My first ever question to a forum was to get information on how to build crosstabs dynamically given a certain structure to work within. I got no answer and eventually found a solution that worked but it was horrific, I might send this in to you when I have read all the relevant (i.e. crosstab) information on your site.

My current problem is that I want to find out what fields are part of a view (no problem), what their ordinal position is (no problem) AND what the source table for that field is (problem).

during my efforts I've used component parts of the INFORMATION_SCHEMA views, chopped up sections of the sp_columns stored procedure and raw information in the system tables.

The two following statements return me generally the information I want to know, but there seems to be no way of joining the output of the two to return a valid list. I know this _must_ be possible but having examined the contents of the system tables on which the INFORMATION_SCHEMA views depend, I still can't find any distinguishing properties to correctly link the information.

WHERE TABLE_NAME='qrySoftwarePackages'


... the first statement returns me 18 rows, correct for the view which I specified, the second statement returns me 25 rows, I assume this is because there are 7 joins and it is returning me records for all of the joined fields as well as the fields which are displayed (i.e. included by the JOIN _not_ the SELECT).

Can you recommend a strategy/method for retrieving this information ? I've completely stripped the INF.. views and sp's down and still can't make it work... equal amounts of me want you to say that it's easy/not-easy...

Mike Edgar."

- Advertisement -