Show all Objects used by Linked Server

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