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
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??
SELECT name, OBJECT_DEFINITION([object_id]) FROM sys.views where OBJECT_DEFINITION([object_id]) like '%.%.dbo.%'