List tables and fields used in Views

[thorny00]

I have over 200 views that I need to track what tables and fields are used in each view. Is there some type of utility that would do this?
I've looked at the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE but I don't understand how to set this up, or if this will even do what I want.

Thanks in advance for your help and time, it is greatly appreciated!

 

[hmckillop]

The INFORMATION_SCHEMA.VIEW_COLUMN_USAGE shows you all the views you have in the current project and a list of all columns
So if you wanted a list containing of only view names and the table columns used in the views you could use something like

select VIEW_NAME, TABLE_NAME, COLUMN_NAME 
from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

"I'm living so far beyond my income that we may almost be said to be living apart

 

[is47501]

I found a utility that does what you need and much more. Including searching any object in the database for text strings, global search and replace and compiling the code.

It is called SQL Editor and you can find it at;

http://www.sqleditor.com/

Check it out and post back your thoughts.

Regards

 

[thorny00]

Thanks HMCKILLOP for pointing me in the right direction. I got what I needed! Below is what I used:
use empower
go
select VIEW_NAME, table_catalog, table_schema, table_name, column_name from
information_schema.VIEW_COLUMN_USAGE
WHERE VIEW_NAME = 'PROBLEM_LOANS'
ORDER BY TABLE_NAME

Also, thanks is47501. Problem is, where I work restricts downloads without an act of God. I will try this at home though.

Thanks again to both of you.

 

[hmckillop]

no problem

"I'm living so far beyond my income that we may almost be said to be living apart