Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: View from an unknown column?

    I have been given a task to get information out of an SQL based server. I was given SQL developer and the "View" in which the information is in.

    with this information I extracted a lot of data, most of which is unnecessary however I found something I was looking for which was a column called
    "Requestor LName". I went looking for the table which included this column however there was no sign on it. I managed to find "Requestor" but none of the other columns that was present from the view (Requestor LName, Requestor ID, Requestor Site and more)
    the column "Requestor" just features numbers possibly refering to something but I am unable to see the relationship between the view and where the view is pulling the data from...

    is there a way I can find this information or how the view is obtaining the information?

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    SET LONG 32767
    
    select text from user_views
    where view_name = 'VIEW_NAME_IN_UPPERCASE';
    This will work in oracle, but are you sure your view isn't in sql server. Typically views are not created with column names that contain spaces.
    Last edited by beilstwh; 03-27-13 at 12:16.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    thanks for the reply. I dont know how to use this code to be honest, ive been pulling the information out by using Excel's VBA to get to the data, could you explain?

  4. #4
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by beilstwh View Post
    This will work in oracle, but are you sure your view isn't in sql server. Typically views are not created with column names that contain spaces.
    um... yes? Im using Oracle SQL Developer (the program) but the connection is to an SQL Server.
    I thought I would post it here because the program was Oracle SQL Developer, I realise now that Oracle is a different database platform.

    I dont know what server version its using either because i was just set the task, i dont know that much about the server itself.

    Should I repost over in "Microsoft SQL Server"? or would it be best to leave it here now?

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I would move it to the Microsoft sql server post. There they can show you how to view where a specific column comes from.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've moved this message thread from the Oracle forum to the Microsoft SQL Server forums for you.

    If you can issue commands to SQL Server (which I think you can using Oracle Developer), then you can use the command:
    Code:
    EXECUTE sp_helptext 'your view name goes here'
    to see the source code for the view.

    Depending on your SQL skills that may be enough to show you what table columns and expressions are used to create the columns of the view. If you can't decipher the SQL that is returned yourself, you can post it and one (or more) of us will help you make sense of it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Requestor LName, Requestor ID, Requestor Site
    knowing stuff all about Oracle or Oracle developer
    but that suggests to me the name of the table is Requestor
    and the columns are
    LName
    ID
    Site
    but then again as I know stuff all about Oracle it may not
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oracle SQL Developer is a thick client tool. It is designed to compete with Toad, and it can be use to send execute SQL statements against a Microsoft SQL Server (like the query tool within SSMS).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    Oracle SQL Developer[/url] is a thick client tool. It is designed to compete with Toad, and it can be use to send execute SQL statements against a Microsoft SQL Server (like the query tool within
    -PatP
    I get the error message:

    Error starting at line 1 in command:
    EXECUTE sp_helptext 'Change Request'
    Error report:
    Incorrect syntax near the keyword 'BEGIN'.

    I dont know what it means really, I think its a compatability issue or something but im not sure what the fix is...
    can you suggest something else?

    thanks!

  10. #10
    Join Date
    Dec 2012
    Posts
    63
    ive been using this:

    Code:
    SELECT * FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like 'Requestor LName' )
    to find this column and its only in the view im looking at (Change Request)
    and one other (ACCESS Change Request)

    it appears that Requestor LName is something created by the view somehow, I need to find out how the view creates it... so I think the right direction is to look at the source code of how the view is populated.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct, the source code is what you need to make sense of this. I would try using:
    Code:
    SELECT *
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE  'Requestor LName' = COLUMN_NAME
    Take the values that are returned from that query (the second and third columns) and insert those values into:
    Code:
    EXECUTE sp_helptext '[TABLE_SCHEMA].[TABLE_NAME]'
    This will return the source code of the view. Although the formatting may need some cleanup, syntactically this source code will be complete and correct.

    If you were using tools that I was more familiar with, there are much easier (GUI based) ways to get the view source code. We're having to go back to basics because I don't know the Oracle SQL Designer very well and have never used it on a Microsoft SQL Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    If you were using tools that I was more familiar with, there are much easier (GUI based) ways to get the view source code. We're having to go back to basics because I don't know the Oracle SQL Designer very well and have never used it on a Microsoft SQL Server.

    -PatP
    pat, to be honest... I hate Oracle SQL Designer, its constantly hanging and freezing and crashing. If there is one you are more familiar with that I could use please let me know, i cant guarentee it would work though bacause I am on a work machine but its worth a shot

    EDIT:
    EXECUTE sp_helptext '[TABLE_SCHEMA].[TABLE_NAME]' doesnt work... its something to do with the execute command, It appears to incompatible.
    Last edited by penfold1992; 03-28-13 at 11:59.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to substitute the TABLE_SCHEMA and TABLE_NAME values that come from my first query in the post above and use them to replace the TABLE_SCHEMA and TABLE_NAME in the EXECUTE statement.

    If you can use SSMS (the SQL Server Management Studio) that is provided by Microsoft at no charge for users of Microsoft SQL Server, it would make things MUCH easier! This tool is specific to MS-SQL (Microsoft SQL Server), but it is the best GUI tool that I've found for accessing MS-SQL.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    You need to substitute the TABLE_SCHEMA and TABLE_NAME values that come from my first query in the post above and use them to replace the TABLE_SCHEMA and TABLE_NAME in the EXECUTE statement.

    -PatP
    the query comes back as:
    TABLE_SCHEMA = _SMDBA_
    TABLE_NAME = Change Request

    so I used this:
    EXECUTE sp_helptext '_SMDBA_.Change Request'

    and it kicked out:
    Error starting at line 1 in command:
    EXECUTE sp_helptext '_SMDBA_.Change Request'
    Error report:
    Incorrect syntax near the keyword 'BEGIN'.


  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    EXECUTE sp_helptext '[_SMDBA_].[Change Request]'
    The square brackets are important, they allow SQL Server to determine object names when those objects use characters that were once forbidden in object names (like spaces in this case).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •