SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/12/2001 :  09:00:24  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.COLUMNS
WHERE 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."

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 07/12/2001 :  11:27:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
This worked for me:

SELECT VCU.TABLE_NAME, VCU.COLUMN_NAME, VCU.VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE VCU
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON (VCU.VIEW_NAME=C.TABLE_NAME AND VCU.COLUMN_NAME=C.COLUMN_NAME)
WHERE VCU.VIEW_NAME='qrySoftwarePackages'


Go to Top of Page

MikeEdgar
Starting Member

United Kingdom
7 Posts

Posted - 07/13/2001 :  09:28:20  Show Profile  Reply with Quote
OK, thanks to robvolk for the reply, but the solution he specifies returns a resultset that isn't valid, my view has 18 fields which display when you execute it, i.e.
SELECT * FROM qrySoftwarePackages
returns 18 columns in the order (left to right) that they occur in the view. What I want is a way of showing the fieldname, source table, ordinal position, and datatype as a rowset, but only for the fields which are actually displayed.

The SQL specified by robvolk returns 25 rows, (and there's a required column 'ORDINAL_POSITION' missing) i.e. we also get data for the 7 join fields which are not a visual part of my query (hence they could not have an ordinal position in the view) If you did select the ordinal position field into the solution provided, then you would get two fields with the same name, sourced from different tables both of which have the same position....

Here's a more simple view with which I can illustrate the problem, there's only one join in the view and hence only the one duplicate field, the view definition is shown immediately underneath, followed by the result sets of various SQL statements.


SELECT tblUsers.*, tblAccessLevels.AccessString FROM tblUsers INNER JOIN tblAccessLevels ON tblAccessLevels.AccessLevel=tblUsers.AccessLevel



SELECT * FROM tblUsers

returns:


UserID UserName Password ConfirmPassword AccessLevel
------- --------- ---------------- ---------------- -----------
1 Admin RSCBS307ESwQM NULL 3


SELECT * FROM qryUsers

returns:

UserID UserName Password ConfirmPassword AccessLevel AccessString
------- --------- ---------------- ---------------- ----------- ------------
1 Admin RSCBS307ESwQM NULL 3 Administrator


but using the (tweaked) solution provided, we get :

TABLE_NAME COLUMN_NAME ORDINAL_POSITION VIEW_NAME
------------------ ------------------ ---------------- ---------
tblUser UserID 1 qryUsers
tblUser UserName 2 qryUsers
tblUser Password 3 qryUsers
tblUser ConfirmPassword 4 qryUsers
tblUser eMailAddress 5 qryUsers
tblUser Phone 6 qryUsers
tblAccessLevel AccessLevel 7 qryUsers
tblUser AccessLevel 7 qryUsers
tblAccessLevel AccessString 8 qryUsers


the join on fieldname causes us to get 9 fields for a view where only 8 should show, the field 'Accesslevel' appears twice with the same position but different source tables.

Ok, this was yet again a long (potentially boring) description of the problem, but hopefully more clear.

cheers,

Mike.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 07/13/2001 :  09:53:11  Show Profile  Visit robvolk's Homepage  Reply with Quote
That's really bizarre, I got no duplicate rows for joined columns, only those in the SELECT list appeared when I ran my original statement.

Here's the SQL for my view (testview):

SELECT ABC.*, golf.golfer
FROM ABC INNER JOIN golf ON ABC.Qty = golf.handi


Here's the query statement I used before (I added the ordinal position and datatype columns to this one and removed view name):

SELECT VCU.TABLE_NAME, VCU.COLUMN_NAME, C.ORDINAL_POSITION, C.DATA_TYPE
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE VCU
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON (VCU.VIEW_NAME=C.TABLE_NAME AND VCU.COLUMN_NAME=C.COLUMN_NAME)
WHERE VCU.VIEW_NAME='testview'


Here's the output I get:

TABLE_NAME COLUMN_NAME ORDINAL_POSITION DATA_TYPE
--------------- --------------- ---------------- ----------
golf golfer 4 varchar
ABC Name 1 char
ABC Name2 3 char
ABC Qty 2 int


The handi column doesn't appear here cause it's not in the SELECT list. I'm using SQL Server 7.0, if you're using SQL 2000 they may have changed the INFORMATION_SCHEMA views. Other than that I'm at a loss.

Go to Top of Page

MikeEdgar
Starting Member

United Kingdom
7 Posts

Posted - 07/16/2001 :  09:06:06  Show Profile  Reply with Quote
Yup, I think that there's a slight difference here, I am joining on fields with the same name, hence when you join the two information schema views based on fieldname there is no duplicate record.

I understood it to be good practise to have primary/foreign keys (or join fields if there is no index) as the same name... I could go through my database changing the join fields to be different names but then some of the core code of the application would have to be rewrittten which is prohibitive on it's own... never mind the fact that I am determined that there must be some way of doing this in SQL server.

Incidentally, I am also using server 7.0 but have a 2000 box in server 7 compatibility mode too, though (on a basic inspection) the schema views seem the same for our purposes..

Having examined the SQL source for both information schema views, I selected all the fields from the relevant system tables and tried to work out how I could link the tables and exclude erroneous records but it seemed impossible.

try :
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE VIEW_NAME='testview'


and it will return all columns which are used by the view, in your example, this would return 5 rows but your fieldname join would stop the fifth and unused column from appearing.

Ok, thanks again, but any more ideas ? Do you think it's even possible ?

Mike

Go to Top of Page

MikeEdgar
Starting Member

United Kingdom
7 Posts

Posted - 07/24/2001 :  12:44:01  Show Profile  Reply with Quote
I've decided it must be possible to find out this information as the management console seems to know which fields are visible and which are not, also which table is the source for that visible field....

does anyone know of a clever way to show changes in the system tables for a before and after type scenario.. that might make it possible....

Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 07/24/2001 :  14:15:51  Show Profile  Reply with Quote
Mike....if you have 2 servers...you'll have 2 sets of system tables...and if you have these, then red-gate.com have s/w which allows 2 databases to be compared...you can download a trial version...it's < 1MB and it may be able to compare system databases....

one of which could be your 'before' and 1 the 'after' version.

I've used it and it's cheap (approx 50$) yet simple and reliable s/w....it might help. or at least you could send them a query..they may answer.

also what about SQL Profiler....could you monitor the commands executed by the user on the management console?

Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 07/26/2001 :  05:46:24  Show Profile  Reply with Quote
If I get this right the problem you have is that if the fields used to join the tables in the view have the same name then you get an extra row in your result, one for each table the field appears in. Can you not just then run a simple group by query picking the minimum value of the field name (arbitrary choice of source table name since it doesn't matter which table the field came from as the view definition requires them to be the same)...

create view columns_in_a_view as
SELECT VCU.TABLE_NAME, VCU.COLUMN_NAME, C.ORDINAL_POSITION, C.DATA_TYPE
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE VCU
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON (VCU.VIEW_NAME=C.TABLE_NAME AND VCU.COLUMN_NAME=C.COLUMN_NAME)
WHERE VCU.VIEW_NAME='test'

select
column_name,
table_name=min(table_name),
ordinal_position=min(ordinal_position),
data_type=min(data_type)
from columns_in_a_view group by column_name


Go to Top of Page

MikeEdgar
Starting Member

United Kingdom
7 Posts

Posted - 07/26/2001 :  06:38:38  Show Profile  Reply with Quote
Hmm, I do require which table sourced the field to be correct, regardless of why this is (because the explanation is long and arduous and to do with c++ and my specific application) I still want it and a simple MIN would be arbitary, if I try to issue an update against a field but the query it came from is not updatable then I need to update it via the base table, (may be bad style) if the field source was wrong I could then easily make a mess of my db by updating the wrong side of a lookup table or if I'm using the other fields from that table to make my record

There has to be a way to do it.

Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 07/26/2001 :  08:33:41  Show Profile  Reply with Quote
OK,

Can't figure out a way of using the system tables or INFORMATION_SCHEMA.

How about this as an alternative.

Grab the SQL used to create the view from INFORMATION_SCHEMA.VIEWS (fieldname is VIEW_DEFINITION). Then parse this statement to extract the source table for each column in the select.

Have a look here to see an example (http://www.stoneygrove.co.uk)

The code I have written will not handle the following situations:-
  • if aliases are used in the view statement
  • if the columns in the select clause involve transformations or functions
  • if * is used in the select clause

A straightforward view will be handled OK

Extra code would ne needed to handle the cases above.

Is this any help at all??????



Edited by - davidpardoe on 07/26/2001 08:34:22

Edited by - davidpardoe on 07/26/2001 08:56:29
Go to Top of Page

MikeEdgar
Starting Member

United Kingdom
7 Posts

Posted - 07/27/2001 :  06:51:05  Show Profile  Reply with Quote
Hmm, I haven't checked the website or the code you recommend yet, I'm sure it would work, but then we do use some *'s and some aliases (though that could change if you only mean field aliases, not view, i.e. a.PCID or tblExeUse.PCID, one would be easy, the other not so good......)

When I evaluated this medthod for myself, the major problem I could see was that I couldn't get the SQL for the query as (for my c++ recordset object) I specify
SELECT * FROM <viewname>
and so stripping that string would be no help at all, what you suggest, i.e. getting the SQL from the information schema views could work, I'll have a look at it, but I would rather avoid doing something so 'barbaric' if you know what I mean, text parsing is prone to errors even when there's a reasonably strict format to follow.

I wonder if the management console uses that method, because it _is_ entirely possible, I just would have thought that there would be some way of getting it from the db in a more intelligent way (and that microsoft would use it for the mmc)... having said that.... I know what horrible bodges I put in my applications, they work, but you're never happy with the way it does it...



Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 07/27/2001 :  09:20:49  Show Profile  Reply with Quote
I've been having another think about this and it seems to me that arbitrarily picking one of the occurrences of the "linking" field should be OK if all you are doing is updating source tables - if the "linking" field is updated in one of the source tables then surely it should be updated in the other source table as well?

I did mean table aliases for the field references (a.fieldname instead of tablename.fieldname). This would require looking up the alias in the from clause.

I know this is the prehistoric way of doing this and I remain as convinced as you that there must be a way to do this...

I'll keep thinking



Edited by - davidpardoe on 07/27/2001 09:22:46
Go to Top of Page

ToddV
Posting Yak Master

USA
218 Posts

Posted - 07/27/2001 :  09:22:21  Show Profile  Reply with Quote
you say, "I wonder if the management console uses that method, because it _is_ entirely possible, I just would have thought that there would be some way of getting it from the db in a more intelligent way (and that microsoft would use it for the mmc)... "
Maybe you could use profiler to find out what MMC is using and try to ue it.
Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 07/27/2001 :  09:32:58  Show Profile  Reply with Quote
On that point where can you see the information you need in MMC?

Go to Top of Page

MikeEdgar
Starting Member

United Kingdom
7 Posts

Posted - 07/27/2001 :  11:40:23  Show Profile  Reply with Quote
Heh, you can see it in the grid pane of design view view of a view, if that makes sense. *8P

Anyway, good plans, I trapped the profiler messages but there's a lot of stuff to read through, haven't seen anything too exciting yet, a lot of sp_columns calls on all the joined tables and the view, also sp_pkeys on the same...

arbitarily choosing a source table is not an option, e.g.

LicenseID in tblSoftwareProducts is a lookup into tblLicenseSoftware where LicenseID is both an identity field and a primary key, you can't update tblLicenseSoftware.LicenseID and must not fail to update tblSoftwareProducts.LicenseID.... if you could update tblLicenseSoftware, then the lookups would all get shot to hell and you'd get multiple records because the joins wouldn't be able to maintain a 1 to 1 or even many to 1 relationship, you'd get a many to many type link which in my case would be disastrous.



Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 07/31/2001 :  08:39:41  Show Profile  Reply with Quote
Sorry no definitive answer yet.
I had a look at the output from SQL Profiler and I think that the MMC does parse the create view statement.
If you use table aliases in the statement then the "table" column in the field list in the design window for the view contains the alias - ie.

create view test as 
select a.field1,b.field2
from table1 a
inner join table2 b on a.id=b.id


will result in a view where the "table" column for field1 and field2 will have a and b rather than table1 and table2.

If you create a calculated field in the view then the "column" column holds the calculation, the "alias" column the new fieldname and the "table" column is left blank:-

create view test as 
select a.field1,b.field2,field3=a.field1+b.field2
from table1 a
inner join table2 b on a.id=b.id


...results in the "column" column holding "a.field1+b.field2" and the "alias" column holding "field3"

I am convinced that the MMC parses the statement!



Go to Top of Page

MikeEdgar
Starting Member

United Kingdom
7 Posts

Posted - 07/31/2001 :  10:40:00  Show Profile  Reply with Quote
Cool, so I'm very happy that it could parse the SQL string, reasons curiously for and against...

...as an sql server it obviously has to be able to parse SQL, so why would they bother stuffing it in a table if fast, efficient stripping/parsing is already a function you support, but oddly, it still stuffs things into the system tables. Now, I can't remember (if I even read any) the history of the system tables, but then it _does_ fill em up with relational information, I guess because it's even faster...

anyway, I can parse the SQL in c++ quite easily as I already did a bunch of stripping, inserting etc, with TOP and BOTTOM and building WHERE/ORDER clauses and even update statements when I have the source tables but the view is not-updatable, however I think that it would be far easier if I could either run an sp_ or just query a table/view for the info in one hit.. I don't know any sql string functions although I've seen some in the books, do you guys know how to parse in SQL ?

Any chance of grabbing the MMC functionality ?

It's good point that the aliased table(s) are not replaced with their correct table names, the MMC doesn't do it either...

Hmm...

M



Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 07/31/2001 :  10:51:56  Show Profile  Reply with Quote
If you take a look at the user defined function and sp at http://www.stoneygrove.co.uk you should pick up some ideas...
It shouldn't take too much effort to adjust the sp to do it all for you...

The most important functions are (you should find most of these familiar)

  • substring(string,start position,number of characters)

  • left(string,number of characters)

  • right(string,number of characters)

  • CHARINDEX: Returns the starting position of expression1 in expression2. Syntax - CHARINDEX ( expression1 , expression2 [ , start_location ] )

  • PATINDEX: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. Syntax - PATINDEX ( '%pattern%' , expression ) - nb. you can use wildcards in PATINDEX (not in CHARINDEX)

  • replace(string1,string2,string3) - search for string2 in string1 and replace it with string3


Hope this gives you a start.




Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000