Skip to Content
0

How to find a view accessing a table in other database

Oct 03, 2017 at 07:58 AM

20

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Mark A Parsons Oct 03, 2017 at 04:56 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded