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.
| Author |
Topic |
|
AskSQLTeam
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.SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='qrySoftwarePackages'SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE VIEW_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." |
|
|
|
|
|
How to get a list of fields with source tables and ordinal positions for a VIEW