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(CrossQueryObject, 1, CHARINDEX('.', CrossQueryObject, 1) - 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.servers) THEN '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