cfKWjbN7

1SELECT DISTINCT NAME INTO #ALL_SPS FROM SYSOBJECTS SO,SYSCOMMENTS SC WHERE SO.ID = SC.ID 
2AND TEXT LIKE '%LINKEDSERVER%'
3
4CREATE TABLE #ERRORS
5(
6    ERRORNUMBER VARCHAR(100),
7    ERRORSEVERITY   VARCHAR(100),
8    ERRORSTATE  VARCHAR(100),
9    ERRORPROCEDURE  VARCHAR(100),
10    ERRORLINE   VARCHAR(100),
11    ERRORMESSAGE VARCHAR(100)
12)
13
14CREATE TABLE #TEMP(SP_NAME VARCHAR(500),DB VARCHAR(100), LINKEDSERVER VARCHAR(100))
15
16BEGIN    
17DECLARE @SP_NAME VARCHAR(MAX)  
18DECLARE @STARTTIME DATETIME =GETDATE()
19
20DECLARE HDR_CURSOR CURSOR FOR   
21
22SELECT NAME FROM #ALL_SPS --IS TEMP TABLE IMPORTED FROM SHARED EXCEL 
23
24OPEN HDR_CURSOR    
25
26FETCH NEXT FROM HDR_CURSOR INTO @SP_NAME
27
28WHILE (@@FETCH_STATUS = 0 )    
29   BEGIN    
30BEGIN TRY
31BEGIN TRANSACTION;
32
33    INSERT INTO #TEMP
34    SELECT  
35    TBLSQLREFERENCEDENTITY.REFERENCED_ENTITY_NAME AS REFERENCEDENTITY,  
36    COALESCE(REFERENCED_DATABASE_NAME,DB_NAME()) AS REFERENCEDDATABASE,  
37    COALESCE(TBLSQLREFERENCEDENTITY.REFERENCED_SERVER_NAME,'CURRENT SERVER') AS REFERENCEDSERVER   
38    FROM SYS.DM_SQL_REFERENCED_ENTITIES('DBO.'+@SP_NAME, 'OBJECT') TBLSQLREFERENCEDENTITY  
39    WHERE  TBLSQLREFERENCEDENTITY.REFERENCED_SERVER_NAME ='LINKEDSERVER'
40    GROUP BY REFERENCED_ENTITY_NAME,REFERENCED_DATABASE_NAME,REFERENCED_SERVER_NAME 
41    COMMIT TRANSACTION;
42END TRY
43BEGIN CATCH
44
45INSERT INTO #ERRORS
46SELECT ERROR_NUMBER() AS ERRORNUMBER
47     ,ERROR_SEVERITY() AS ERRORSEVERITY
48     ,ERROR_STATE() AS ERRORSTATE
49     ,ERROR_PROCEDURE() AS ERRORPROCEDURE
50     ,ERROR_LINE() AS ERRORLINE
51     ,ERROR_MESSAGE() AS ERRORMESSAGE;
52
53END CATCH
54  FETCH NEXT FROM HDR_CURSOR INTO @SP_NAME
55
56 END    
57 CLOSE HDR_CURSOR    
58 DEALLOCATE HDR_CURSOR    
59 SELECT DATEDIFF(MS,@STARTTIME,GETDATE()) 'TIME TAKEN IN MS'
60 SELECT DISTINCT * FROM #TEMP
61 SELECT DISTINCT * FROM #ERRORS
62
63END
64	
65...
66BEGIN TRANSACTION;
67
68--see if that object exists in your linked server...
69if(select 1 
70   from  YourLinkedServer.master.sys.objects
71   where [name] = @SP_NAME) is not null
72
73
74--if it exists, then insert from it...
75INSERT INTO #TEMP
76SELECT  
77TBLSQLREFERENCEDENTITY.REFERENCED_ENTITY_NAME AS REFERENCEDENTITY,  
78COALESCE(REFERENCED_DATABASE_NAME,DB_NAME()) AS REFERENCEDDATABASE,  
79COALESCE(TBLSQLREFERENCEDENTITY.REFERENCED_SERVER_NAME,'CURRENT SERVER') AS REFERENCEDSERVER   
80FROM SYS.DM_SQL_REFERENCED_ENTITIES('DBO.'+@SP_NAME, 'OBJECT') TBLSQLREFERENCEDENTITY  
81WHERE  TBLSQLREFERENCEDENTITY.REFERENCED_SERVER_NAME ='LINKEDSERVER'
82GROUP BY REFERENCED_ENTITY_NAME,REFERENCED_DATABASE_NAME,REFERENCED_SERVER_NAME 
83
84COMMIT TRANSACTION;
85
86...