Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    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:

    Code:
    SELECT VIEWS.name AS VIEW_NAME, 
     	   TABLES.name AS TABLE_NAME, 
     	   VIEW_SQL.text
     FROM sysobjects VIEWS 
     INNER JOIN
     	 syscomments VIEW_SQL
      ON VIEWS.id = VIEW_SQL.id 
     INNER JOIN
     	 sysobjects TABLES
      ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%'
     WHERE (VIEWS.xtype = 'V')
      AND (TABLES.xtype = 'U')
     ORDER BY VIEWS.name, TABLES.name
    Justin

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Look for SQL Dependency Viewer from Red-Gate software. It's a free download (though I think it's still beta).

    http://www.red-gate.com/products/sql_dependency_viewer/

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Quote Originally Posted by hmscott
    Look for SQL Dependency Viewer from Red-Gate software. It's a free download (though I think it's still beta).

    http://www.red-gate.com/products/sql_dependency_viewer/

    Regards,

    hmscott
    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.

    Regards
    Justin

Posting Permissions

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