Post reply

Query for listing views in a database involving tables from other databases

  • October 19, 2015 at 9:26 am

    #305530

    Hello, Can anyone provide me with a script to get the list of views in a database, involving tables from other databases??...appreciate your help..am using SQL server 2014

  • October 19, 2015 at 9:40 am

    #1834119

    There is a system table called sys.sql_expression_dependencies which contains references between objects, for example when a view or stored procedure references tables. If referenced_database_name is not null, then the referenced object is from another database, and if referenced_server_name is not null, then the object is from another server (ie: referenced via a linked server).

    SELECT object_name(referencing_id)object_name

    , referenced_server_name

    , referenced_database_name

    , referenced_schema_name

    , referenced_entity_name

    FROM sys.sql_expression_dependencies

    WHERE referenced_database_name is not null;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • October 19, 2015 at 10:07 am

    #1834131

    Thanks much Eric...this was helpful..I just made slight changes to suit my requirement

    select

    object_name(referencing_id) as ViewName

    ,o.type_desc

    ,referenced_database_name + '.' + referenced_schema_name + '.' + referenced_entity_name as ReferencedTable

    --,*

    from sys.sql_expression_dependencies d

    inner join sys.objects o on o.object_id = d.referencing_id

    where referenced_database_name is not null and is_ambiguous = 0 and type_desc = 'VIEW'

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

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