Finding all views that are using linked server

Question

I am updating url for linked servers. Before make the changes, I would like to know all views that have reference to this linked servers. Is there any programmatic way (TSQL) to perform this task?

Thanks for your help.

I am using SQL Server 2005, 2008 and 2012. The database servers that referencing linked servers are mostly SQL Server 2005

Answer 1

While it may return false positives, and won't capture any cases where a four-part name is constructed using dynamic SQL, this is probably the simplest approach:

SELECT name FROM sys.views
WHERE LOWER(OBJECT_DEFINITION([object_id])) LIKE LOWER('%LinkedServerName%');