Unanswered: Identyfying which tables have gone into a view
I am trying to find the list of tables that make up any particular view in SQL server 2000. The information schema - VIEW_TABLE_USAGE would be the best tool, if only the sysdepends table worked! When I use the schema I get some but not all of my views.
Has anyone got a solution, prefably one without cursors, that can identitfy the source tables for a view?
I have used the following SQL, but unfortunately it gives too many results:
SELECT VIEWS.name AS VIEW_NAME,
TABLES.name AS TABLE_NAME,
FROM sysobjects VIEWS
ON VIEWS.id = VIEW_SQL.id
ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%'
WHERE (VIEWS.xtype = 'V')
AND (TABLES.xtype = 'U')
ORDER BY VIEWS.name, TABLES.name
Thanks, I have tried this tool and it should be good when its complete, however I actually want the names of the tables rather then a graph view. I am using the table names to provide some meta data for a database I am developing.