Post reply

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

  • May 28, 2015 at 3:31 pm

    #318664

    Is there any script to find out the list of database object that reference to a particular server? I am doing investigation. I would really appreciate your help!

  • May 29, 2015 at 12:42 am

    #1801479

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply