SA0252 : The referenced object (table, view, procedure or function) is in another database

Introduction

Identifying cross-database queries can help prevent unforeseen performance issues and ensure consistency.

Description

Cross-database queries in T-SQL code are those that reference objects located in a different database from the one currently used. While SQL Server supports such queries, they may introduce challenges such as increased latency and complications with backup and restore operations.

For example:

-- Example of a cross-database query
SELECT * FROM OtherDatabase.dbo.TableName;

This query accesses a table in a different database. Such cross-database references can be problematic due to:

Potential for increased execution time due to additional network overhead and cross-database calls.
Complications during database backup and restore processes, especially when databases are restored separately.

How to fix

Improve maintainability and performance by using synonyms instead of hardcoded cross-database references.

Follow these steps to address the issue:

1.Identify the cross-database query within your T-SQL code that uses direct object references in other databases. For example, a query that accesses OtherDatabase.dbo.TableName .

2.Create a synonym in your current database for the external object. Use the CREATE SYNONYM statement to define the synonym for the cross-database object.

3.Replace the direct cross-database object references in your T-SQL code with the newly created synonym. This change will enhance code maintainability and optimize performance.

For example:

-- Create a synonym for the cross-database reference
CREATE SYNONYM SynonymTableName FOR OtherDatabase.dbo.TableName;

-- Use the synonym in place of a direct cross-database reference
SELECT * FROM SynonymTableName;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

20 minutes per issue.

Additional Information

Synonyms (Database Engine)

Example Test SQL

SELECT
       *,Database1..Table4.Column1
FROM
     ..Table1,
     Schema1.Table2, Database1..Table3
     INNER JOIN Database1..Table4
        ON Database1..Table4.Column1=Database1..Table3.Column1 AND
           Database1..Table4.Column2=Database1.Schema1.Table1.Column2
     INNER JOIN Database1.Schema2.Table4 ON
           Database1..Table2.Column1=Database1..UserDefinedFunction(1) AND
           Database1..Table2.Column2=Database1.Schema1.Table1.Column2
WHERE
      Database1.Schema1.Table1.Column1=Database1.Schema1.Table2.Column1 AND
      Database1.Schema1.Table3.Column1=Database1.Schema1.Table2.Column3
EXECUTE database1.schema1.proc1
EXECUTE adventureWorks2008r2_test.schema1.proc1

Analysis Results

	Message	Line	Column
1	SA0252 : The referenced object Database1..Table3 is in another database.	5	21
2	SA0252 : The referenced object Database1..Table4 is in another database.	6	16
3	SA0252 : The referenced object Database1.Schema2.Table4 is in another database.	9	16
4	SA0252 : The referenced object database1.schema1.proc1 is in another database.	15	8

Analysis Rules