Find queries, SP's or jobs that use a linked server?

  • Any help is greatly appreciated. I have a SQL2005 server that uses a "linked server" setup to an old SQL 2000 server. I am retiring this old server and setting up a new SQL 2008 server.

    My developers are not sure of all the instances where they are referencing this old SQL2000 server, whether it's in a query, a stored procedure, or a scheduled job that may be calling this old server.

    Is there a way to search inside of them to find any reference to the linked server name so that we can be sure we've migrated everything over properly?

    Any help is greatly appreciated. Thanks!

  • You should be able to see the linked servers listed in Management Studio for all instances. As for searching across them all, you have to connect to each and then search.

    There are some tools, like Central Management Servers from MS, or SQL MultiScript from Red Gate, that can help automate a query across multiple servers.

    You could possibly also use SQLCMD or Powershell and one of the scripts on this site that searches all objects to look for some text.

    If you have the code in Source Control (and you should), usually there are global search functions in there as well.

    Disclosure: I work for Red Gate

  • Sorry, not sure I explained it well. In my SQL 2005 instance, there are queries and stored procedures within 2 or 3 databases. Inside these queries for example, it will call a table in the linked server (sql 2000). I'm trying to find an easy way to search across all queries (or SP's or jobs) to find any references to a database in the linked server so that we can update it with the new server name.

    thanks again!!

  • Try this

    Declare @VName varchar(256)

    Declare Findlinked cursor

    LOCAL STATIC FORWARD_ONLY READ_ONLY

    FOR

    Select name

    From sys.servers

    Where is_linked = 1

    Open Findlinked;

    Fetch next from Findlinked into @VName;

    while @@FETCH_STATUS = 0

    Begin

    SELECT OBJECT_NAME(object_id)

    FROM sys.sql_modules

    WHERE Definition LIKE '%'+@VName +'%'

    AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;

    Fetch next from Findlinked into @VName;

    End

    Close Findlinked

    Deallocate Findlinked

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • that works awesome! Thanks so much! Will that find it in a scheduled job as well?

  • only in procs and funcs

    If the scheduled job uses ad-hoc queries (not stored procedures), then the code would need modified.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Ok, thanks. I'm pretty sure they use ad-hoc queries. How would I modify it? Some jobs just use SQL queries, others I see have steps that use an SSIS package where the data sources are listed.

    thanks again!

  • SSIS will be something totally different. But here is the mod for the agent jobs.

    Declare @VName varchar(256)

    Declare Findlinked cursor

    LOCAL STATIC FORWARD_ONLY READ_ONLY

    FOR

    Select name AS name

    From sys.servers

    Where is_linked = 1

    Open Findlinked;

    Fetch next from Findlinked into @VName;

    while @@FETCH_STATUS = 0

    Begin

    SELECT OBJECT_NAME(object_id)

    FROM sys.sql_modules

    WHERE Definition LIKE '%'+@VName +'%'

    AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;

    Fetch next from Findlinked into @VName;

    END

    Close Findlinked

    Open Findlinked;

    Fetch next from Findlinked into @VName;

    while @@FETCH_STATUS = 0

    Begin

    SELECT j.name AS JobName,js.command

    FROM msdb.dbo.sysjobsteps js

    INNER JOIN msdb.dbo.sysjobs j

    ON j.job_id = js.job_id

    WHERE js.command LIKE '%'+@VName +'%'

    Fetch next from Findlinked into @VName;

    END

    Close Findlinked

    Deallocate Findlinked

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 8 posts - 1 through 7 (of 7 total)

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