Skip to Content

How to find a view accessing a table in other database

I have views in my database and some of them are accessing a table in other database:

create view v1 as select a, b from otherdb..t1

How do I find a view, or other compiled objects, that accesses a table not exists in current database?

sp_depends does't show dependency when table and view are in different databases.

Do I have to read a source code in syscomments?

Regards,
Kazuo Otani

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 03, 2017 at 04:56 PM

    Your primary method will be to parse the syscomments table in the database where the view resides, but keep in mind that syscomments splits source code into chunks of 255 characters with the net result being that your source table name could be split across 2 syscomments records.

    So your best bet is to rebuild the source from syscomments and then run your favorite string/pattern matching method against said reconstituted source.

    There are a couple options (off the top of my head) for reconstituting the source code:

    1. exec sp_helptext <view_name>,null,null,'showsql'
    2. use ddlgen (OS-level utility) to reverse engineer the source code

    ----------

    Another, slightly convoluted, option that comes to mind:

    set showplan on
    go
    select * from <view_name> where 1=2
    go

    ... the query plan should reference the table(s) that make up the view definition, at which point you're back to running your favorite string/pattern matching method against the query plan.

    Add comment
    10|10000 characters needed characters exceeded