Technical Article

Show all Objects used by Linked Server

,

This script will require you to have read permissions on system views (sys.servers, sys.sql_modules, sys.objects). To create the procedure simply run the code above. To run the comments contain two examples of how to run. Simply put in the procedure name followed by either null or the name of the linked server you wish to run against.

create proc usp_get_linked_server_use @LinkedServerName varchar(800)
as
/******************************************************************************

*    File Name:        dbo.usp_get_linked_server_use

*    File Desc:        Will return all objects, and object type for the Linked 

* Server Name given. If no name is provided code will run for 

*                    all linked servers on the box

*    Database:        DBAADMIN

*    Language:        MS Transact-SQL 

*    Version:        MS SQL 2005-2008r2

*                    

*    Views:            sys.servers,sys.sql_modules,sys.objects

*                         

*    Procedures:        N/A

*                    

*    Date:            8/1/2012

*

*    Architect:        Kiley Milakovic

*    Project Mgr:    N/A

*    Project ID:        N/A

*                    

*    Notes:            

*        1. Copyright (c) 2012 Developers IT, LLC

*                    

*    Special Comments/Warnings

*        [to run] usp_get_linked_server_use 'your linked server name here'        

*                 usp_get_linked_server_use null    

*                    

*******************************************************************************

*    Version:    xxxxx            Author: xxxxxxxxxxxxx

*    Date:        xx/xx/xxxx

*******************************************************************************

*    Description of Requests:

*        1. xxxxxxx

*                    

*    Description of Modifications:

*        1. xxxxxxx

*                    

*    Special Comments:

*     None        

*                    

*    Other modules changed with this request:

*        None        

*******************************************************************************/
declare @loopcount bigint,

        @HoldServ varchar(800),

        @linkcount int

        

create table #holdlinked

(

    tabid             bigint identity(1,1),

    LinkedServerName varchar(800)

)



create table #holdinfo

(

    tabid                bigint identity(1,1),

    LinkedServerName    varchar(800),

    ObjectName            varchar(800),

    ObjectType            varchar(200)



)

/******************************************************************************

*Get Server(s) list        

*******************************************************************************/
if isnull(@LinkedServerName,'0') = '0'

    begin

        insert into #holdlinked(LinkedServerName)

        select name

        From sys.servers

        Where is_linked = 1

    end

else

    begin

     insert into #holdlinked(LinkedServerName)

        select name

        From sys.servers

        Where [name] = @LinkedServerName and is_linked = 1    

    end



select @linkcount = count(1)

from #holdlinked

/******************************************************************************

*Process Objects    

*******************************************************************************/
set @loopcount = 1

while @loopcount <= @linkcount

begin



    select @HoldServ = LinkedServerName

    from #holdlinked

    where tabid = @loopcount



 insert into #holdinfo(LinkedServerName,ObjectName,ObjectType)

 SELECT @HoldServ,OBJECT_NAME(sm.object_id),so.[type_desc]

    FROM sys.sql_modules sm inner join sys.objects so on sm.object_id = so.object_id

    WHERE Definition LIKE '%'+@HoldServ +'%' AND (OBJECTPROPERTY(sm.object_id, 'IsProcedure') = 1 or

         OBJECTPROPERTY(sm.object_id, 'IsScalarFunction') = 1 or

         OBJECTPROPERTY(sm.object_id, 'IsTable') = 1 or

         OBJECTPROPERTY(sm.object_id, 'IsTableFunction') = 1 or

         OBJECTPROPERTY(sm.object_id, 'IsView') = 1 or

         OBJECTPROPERTY(sm.object_id, 'IsUserTable') = 1 )

    set @HoldServ = null

    set @loopcount = @loopcount + 1

end

/******************************************************************************

*Show Results    

*******************************************************************************/
select LinkedServerName,ObjectType,ObjectName

from #holdinfo

order by LinkedServerName,ObjectName

Rate

2.5 (2)

Share

Share

Rate

2.5 (2)