DBA - How To Find Objects ( Stored Procedures, Views etc) In SQL Server Which Are Using Cross Database or Cross Server Queries

We often come across this problem where we have to get more information about SQL Server objects such as Stored Procedure, Views etc if they are using Cross Server (Linked Server) or Cross Database queries. This can be very common scenario when we are planning to migrate some of the databases from one SQL Server to other SQL Server. In those cases we want to make sure we are taking care of Cross Database and Cross Server queries which are used in different objects as part of SQL Server Database Migration.

The below script is going to return you following information

ObjectName:
Name of Object such as Stored Procedure, View etc.
Type: 
Type of SQL Server Object ( P for Stored Procedure, V for View etc.)
ObjectTypeDescription:
Full description of Object such as Stored Procedure,View etc.
CrossQueryObject: 
Will return you fully qualified name
CrossQueryType :
This will return you three values depending upon the analysis.
i) Cross Database
ii) Cross Server
iii) Using Fully Qualified Name for Current Database
ObjectDefinition:
You will have object definition so you can do further analysis

USE [TestDB] --> Change the database name as per your Requirements
GO
DECLARE @TempTable TABLE
  (
     ObjectName       VARCHAR(500),
     CrossQueryObject VARCHAR(500),
     CrossQueryType   VARCHAR(500),
     FirstValue       VARCHAR(100),
     OBJECT_ID        INT
  )DECLARE @SP_Object_ID AS VARCHAR(50)DECLARE Object_Cursor CURSOR FOR
  SELECT OBJECT_ID
  FROM   sys.sql_modules sm
OPEN Object_Cursor
FETCH Next FROM Object_Cursor INTO @SP_Object_ID
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SP_Definition_XML XML-->Convert the definition into XML
      SELECT @SP_Definition_XML = CAST ('<M>'
      + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      REPLACE(definition, '&', ''),
       CHAR(13), ' '),
       CHAR(10), ' '),
       '<', ''), '>', ''), '', ''), '''', ''), ',', ' '),
        ' ', '</M><M>')          + '</M>' AS XML)   
FROM   sys.sql_modules
      
WHERE  OBJECT_ID @SP_Object_ID-->Converting XML into Table
      
;
      
WITH CTE_Table
           
AS (SELECT t.value('.''VARCHAR(500)'AS Col
               
FROM   @SP_Definition_XML.nodes('/M'AS x(t)),
           
CTE_NumOfPeriods
           
AS (SELECT OBJECT_NAME(@SP_Object_ID)            AS ObjectName,
                      
LEN(Col) - LEN(REPLACE(Col'.''')) NumOfPeriods,
                      
Col                                   AS CrossQueryObject
               
FROM   CTE_Table
               
WHERE  LEN(Col) - LEN(REPLACE(Col'.''')) >= 2
                      
AND Col NOT LIKE '%=%'),
           
CTE_Table1
           
AS (SELECT ObjectName,
                      
CrossQueryObject,
                      
RTRIM(LTRIM(REPLACE(REPLACE(SUBSTRING(CrossQueryObject1CHARINDEX('.'CrossQueryObject1) - 1), ']'''), '['''))) FirstPart
               
FROM   CTE_NumOfPeriods)-->Insert into Temp table  
      
INSERT INTO @TempTable
      
SELECT *,
             
@SP_Object_ID
      
FROM   (SELECT ObjectName,
                     
CrossQueryObject,
                     
CASE
                       
WHEN FirstPart IN (SELECT name
                                          
FROM   sys.serversTHEN 'Cross Server'
                       
WHEN FirstPart IN (SELECT name
                                          
FROM   sys.databases
                                          
WHERE  name <> DB_NAME()) THEN 'Cross Database'
                       
WHEN FirstPart DB_NAME() THEN 'Using Fully Qualified Name for Current Database'
                       
ELSE 'Not Valid'
                     
END QueryType,
                     
FirstPart
              
FROM   CTE_Table1
              
WHERE  FirstPart NOT IN (SELECT name
                                         
FROM   sys.schemas) )) DR
      
WHERE  DR.QueryType <> 'Not Valid'

      
PRINT OBJECT_NAME(@SP_Object_ID)
            + 
' analysis is Completed'

      
FETCH Next FROM Object_Cursor INTO @SP_Object_ID
  
END

CLOSE 
Object_Cursor
DEALLOCATE Object_Cursor
SELECT T.ObjectName,
       
o.TYPE,
       
o.type_desc   AS ObjectTypeDescription,
       
T.CrossQueryObject,
       
T.CrossQueryType,
       
sm.definition AS ObjectDefinition
FROM   @TempTable T
       
INNER JOIN sys.objects o
               
ON T.OBJECT_ID o.OBJECT_ID
       
INNER JOIN sys.sql_modules sm
               
ON T.OBJECT_ID sm.OBJECT_ID



Snapshot for Output