Post reply

To get of tables used in View

  • May 7, 2016 at 6:39 pm

    #149042

    How do I get of list of tables used in a view? I'm using 2005 sql server. Is there any DMVs that does this?

  • May 8, 2016 at 7:21 am

    #1877356

    there's a pair of functions that you can use to get either the things the views reference, or the things that the view consumes.

    --objects that USE the views

    SELECT

    schema_name(vwz.schema_id) As SchemaName,vwz.Name as viewName,fn.*

    from sys.views vwz

    cross apply sys.dm_sql_referencing_entities (quotename(schema_name(vwz.schema_id)) + '.' + quotename(vwz.name), 'OBJECT') fn

    --objects like tables or other views the views reference

    SELECT

    schema_name(vwz.schema_id) As SchemaName,vwz.Name as viewName,fn.*

    from sys.views vwz

    cross apply sys.dm_sql_referenced_entities (quotename(schema_name(vwz.schema_id)) + '.' + quotename(vwz.name), 'OBJECT') fn

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply