Search SQL Objects like Views, Funactions and SPs those contain Linked Server?

Question

Hi All,

I was looking for query to find out list of Views, Funactions and SPs those contain Linked Server and wrote this query.

Is there any other way to do this..?

***--Query to Find List SP, VIEW and FUNCTIONs Where Server Name and Database Name has been used
sp_MSforeachdb
'SELECT DISTINCT
"?" AS DatabaseName,
O.NAME AS SP_NAME,
O.XTYPE AS TYPE,
CASE
WHEN C.TEXT LIKE ''%[MicrosoftDynamicsAX%''{ESCAPE ''\''}
THEN ''%[MicrosoftDynamicsAX%''
WHEN C.TEXT LIKE ''%[MicrosoftDynamicsAX%''{ESCAPE ''\''}
THEN ''%[MicrosoftDynamicsAX%''
WHEN C.TEXT LIKE ''%\MicrosoftDynamicsAX%''{ESCAPE ''\''}
THEN ''%MicrosoftDynamicsAX%''
WHEN C.TEXT LIKE ''%MicrosoftDynamicsAX%''
THEN ''%MicrosoftDynamicsAX%''
END MatchingPattern,
C.TEXT AS QUERY_TEXT

FROM ?..SYSCOMMENTS C
INNER JOIN ?..SYSOBJECTS O ON C.ID = O.ID
WHERE(
C.TEXT LIKE ''%[MicrosoftDynamicsAX%''{ESCAPE ''\''}
OR C.TEXT LIKE ''%[MicrosoftDynamicsAX%''{ESCAPE ''\''}
OR C.TEXT LIKE ''%\MicrosoftDynamicsAX%''{ESCAPE ''\''}
OR C.TEXT LIKE ''%MicrosoftDynamicsAX%''
)
ORDER BY 2,
1'***

https://stackoverflow.com/questions/1025022/find-an-object-in-sql-server-cross-database

Answer 1

Hi @Shivendoo Kumar ，

Please refer below and check whether it is a little helpful. Thanks.

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   
 OR OBJECTPROPERTY(object_id, 'IsTableFunction') = 1   
 OR OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1   
 OR OBJECTPROPERTY(object_id, 'IsView') = 1 )  
 Fetch next from Findlinked into @VName;  
End  
Close Findlinked  
Deallocate Findlinked  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Shivendoo Kumar ，

Please also refer below:

SELECT name FROM sys.views  
WHERE LOWER(OBJECT_DEFINITION([object_id])) LIKE LOWER('%LinkedServerName%');  
  
SELECT name FROM sys.procedures  
WHERE LOWER(OBJECT_DEFINITION([object_id])) LIKE LOWER('%LinkedServerName%');  
  
SELECT *  
FROM sys.objects  
WHERE type IN ('FN', 'IF', 'TF')  
and LOWER(OBJECT_DEFINITION([object_id])) LIKE LOWER('%LinkedServerName%');  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I have a article on my web site where I discuss various way to search source code, http://www.sommarskog.se/sqlutil/SearchCode.html. There are a couple of options you can explore.