Finding all views that are using linked server

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

Answers


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%');

This will find the views:

SELECT t2.name, OBJECT_DEFINITION(t1.[object_id]) view_definition  
FROM sys.views t1 join sys.servers t2 on 
OBJECT_DEFINITION(t1.[object_id]) like '%['+ t2.name + '].%' ESCAPE '['

It can fail if a table, view, schema or database has same name as a linked server.

In case some views have eluded the first check you can add this line this part is not checking for the square brackets surrounding the linked server name. But be aware that this part is more likely to include extra unwanted views

or OBJECT_DEFINITION(t1.[object_id]) like '% '+ t2.name + '.%'

EDIT: Changed sys.sysservers to sys.servers. Thanks Aaron Bertrand


If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??


Try This:

SELECT name, OBJECT_DEFINITION([object_id]) FROM sys.views
where OBJECT_DEFINITION([object_id]) like '%.%.dbo.%'

Need Your Help

Java vectors without generics

java generics vector

I have this question and I'm unsure on what exactly it is asking, i've posted the whole question but i dont expect the answer, just someone to point me in the right direction please!

with SIP, when to use TCP not UDP?

tcp udp sip voip

I know pretty the differences between UDP and TCP in general (eg. http://www.onsip.com/about-voip/sip/udp-versus-tcp-for-voip)