November 18, 2004 at 11:46 am
Given a view name, I'd like to run a script that will return the name of each field in the view, and the
.[field] that each field in the view is from. I'm immediately drawn to sysobject, syscolumns, and sysdepends, but I can't seem to tie it all together to give the result I'm looking for.
Does anyone have a script that does that...?
November 18, 2004 at 12:08 pm
Take a look at INFORMATION_SCHEMA.VIEW_COLUMN_USAGE in Books online.
Steve
November 22, 2004 at 2:28 pm
Thank you Steve,
I looked over the INFORMATION_SCHEMA views, but none seemed to give me the table.field or view.field that a given view's fields are sourced from.
If I have a view defined as
Create View MyView AS SELECT LastName = Last_Name FROM MyTable.
What I'm trying to retreive is something like this:
ThisViewName ThisViewFieldName SourceTableName SourceFieldName
MyView LastName MyTable Last_Name
The closest I've been able to get is the stored view def.
Any hints?
November 22, 2004 at 8:41 pm
OH! You can script a view, and get the source columns/tables that way. In Enterprise Manager, right click, all tasks, generate SQL script. Click "Show All", select your view from the list and click Add. Then click Preview to see the script.
Or in Query Analyzer Object Browser, right click and script to new window.
Steve
edit...
Oh wait. You said script. Back to the drawing board.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply