RE: List of databases have objects (like views or stored procs?) that reference a server

SSC Guru

Points: 61136

May 29, 2015 at 12:42 am

Not clear what you mean by:

reference to a particular server?

If you are talking about checking Objects on other servers you can loop through the server you are on as well as using a combination of the sys.servers, the Information_Schema and dynamic SQL. If you are saying that you want to look at objects that have a specific server references in their DDL you could do something like

SELECT OBJECT_TYPE = 'VIEW', * FROM INFORMATION_SCHEMA.VIEWS

WHERE VIEW_DEFINITION LIKE '%<whateverserver>%'

UNION ALL

SELECT 'ROUTINE' FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%<whateverserver>%'

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001