2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wei Ma
  • 3,125
  • 6
  • 44
  • 72
  • possible duplicate of [View linked server dependencies sql server 2008](http://stackoverflow.com/questions/6451566/view-linked-server-dependencies-sql-server-2008) – gbn Aug 02 '12 at 13:33
  • are you going to accept answer 3? – DtechNet Jun 25 '19 at 14:40

4 Answers4

5

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%');
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • @t-clausen.dk could you explain please? – Aaron Bertrand Aug 02 '12 at 13:25
  • i tried it in one of our database, 4 out of 4 views with linkedservers were not found – t-clausen.dk Aug 02 '12 at 13:26
  • 2
    @t-clausen.dk Did you change `LinkedServerName` to the actual name of your linked server??? – Aaron Bertrand Aug 02 '12 at 13:27
  • well i expect the OP has serveral linked servers, "This linked Servers" and "url for linked servers". I read it as plural. Not really handy to hardcode when more linkedservers are added. – t-clausen.dk Aug 02 '12 at 13:31
  • @t-clausen.dk: check for case sensitive collation before saying "no". Or COLLATE to all lower case. I *KNOW* this works: http://stackoverflow.com/questions/6451566/view-linked-server-dependencies-sql-server-2008/6451587#6451587 – gbn Aug 02 '12 at 13:32
  • @t-clausen.dk I still don't understand, even if we ignore the confusing language (e.g. `this linked servers`). If you have multiple linked servers to check, you can add an OR. – Aaron Bertrand Aug 02 '12 at 13:32
  • @AaronBertrand as I mentioned earlier, you are expecting hardcoding of the linked server names. Not really a flexible solution, I am currently looking at a database with 24 linked servers, some of those linked servers has names after customer abbreviations that would be 23 or statements plus those for new linked servers. Some of our views contain those same customers hardcoded, so those would also show up in your search along with any combination of those 3 letter abbreviations (imagine the hits in a view on a customer with the abbreviation 'sel'). – t-clausen.dk Aug 02 '12 at 14:50
  • @t-clausen.dk to be fair, the question wasn't about 24 linked servers. I'm glad you have a better solution for your scenario, but if you name your linked servers `sel` you deserve a difficult time with it. – Aaron Bertrand Aug 02 '12 at 14:56
  • +1 for spending time pointing our the flaws in my solution and for your solution that can handle many situations. – t-clausen.dk Aug 02 '12 at 15:45
3

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

t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • Why sys.sysservers instead of sys.servers? And what if you are looking for a specific 24 linked servers but you have 100 linked servers defined? Also the leading space in your second clause will miss references where it is preceded by a tab or carriage return / line feed. – Aaron Bertrand Aug 02 '12 at 14:59
  • I used sys.sysservers because i didn't realize it was the old way, thanks for pointing that out. QA specified: find views with linked servers, so that is what i tried to do. I have never seen or made a linebreak, space or tab between linked server, database, schema or column. But now that you mention it i realize it is possible, I sure didn't anticipate that could happen. When I used your version, I had 43 rows returned when only 11 had linked servers. So I guess both solutions have flaws. – t-clausen.dk Aug 02 '12 at 15:16
  • I don't think there is a perfect solution. That's why I prefaced my solution with the note about false positives, dynamic SQL, etc. and why I was disappointed with your vague "this will not catch it" comment. – Aaron Bertrand Aug 02 '12 at 15:21
  • you need to change srvname to [name] - srvname doesn't exist on sys.servers – DtechNet Jun 25 '19 at 14:36
  • Also for me the ESCAPE '[' part doesn't work for me. When I remove it then it works fine. So whoever is using this depending on if you're using the brackets or not just keep that in mind. – DtechNet Jun 25 '19 at 14:40
  • @DtechNet the column name has changed from srvname in 2005 to name in 2008 or 2012. So correct it is name, not srvname. I don't agree with your second comment - **ESCAPE** worked fine for me. When removing escape, you get some arbitrary result. col like '%[abcd]%' will find any column that contains a,b,c or d – t-clausen.dk Jun 26 '19 at 07:20
2

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).

enter image description here

enter image description here

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??

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
-1

Try This:

SELECT name, OBJECT_DEFINITION([object_id]) FROM sys.views
where OBJECT_DEFINITION([object_id]) like '%.%.dbo.%'
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
  • or OBJECT_DEFINITION([object_id]) like '%.%.[dbo].%' – t-clausen.dk Aug 02 '12 at 13:21
  • This is different from my answer (and better) because you've hard-coded the dbo schema and return *any* four-part name (including other linked servers you're not looking for)? I'm so confused right now. – Aaron Bertrand Aug 02 '12 at 13:26
  • @AaronBertrand you are totally right, this can fail. If the view is defined like this: 'select * from database.dbo.field join database.dbo.field on 1 = 1' this fails – t-clausen.dk Aug 02 '12 at 13:37