List Everything that uses a linked server in sql

Question

I am currently facing an issue communicating with a SQL server which has been moved and the database which I was looking info has been removed. I have been using an alias manager to divert all traffic from the old server to the new server, but still need it to look at the new table in this new server. Is it somehow possible to list everything that uses this linked server?

Answer 1

The are system views sys.sql_modules. The definition column contains the create script for the object. You could run this on each database:

select object_name(object_id) from sys.sql_modules where definition like '%LINKED_SERVER_NAME%';