SA0253 : The current database is hardcoded in object reference

Introduction

Avoid using database-specific object identifiers in SQL statements to ensure script portability and maintainability.

Description

Directly referencing the current database within your SELECT , UPDATE , DELETE , INSERT , MERGE , and EXECUTE statements by using fully qualified names can lead to future complications. This arises when such hardcoded references make your SQL scripts less flexible and harder to maintain.

For example:

-- Example of problematic query with hardcoded database reference
SELECT * FROM CurrentDBName.SchemaName.TableName;

Using the database name directly in queries can be problematic if you move the code to another database or change the database name. This practice hinders the portability of your code across different environments and complicates updates.

Reduces script portability when deploying code across multiple databases.
Increases difficulty in maintaining codebase, especially during database migrations or name changes.

How to fix

Avoid using hardcoded database identifiers in SQL statements to improve script portability and maintainability.

Follow these steps to address the issue:

1.Identify any SQL statements that include hardcoded database names within the object identifiers, such as "CurrentDBName.SchemaName.TableName" .

2.Remove the database name part from the identifiers to allow SQL Server to automatically resolve to the current database at runtime. This will replace "CurrentDBName.SchemaName.TableName" with "SchemaName.TableName" .

3.Review the remaining schema names to ensure they are necessary or appropriately scoped for your queries.

4.Test the modified queries to ensure they function as expected without the hardcoded database context.

For example:

-- Example of corrected query without the database reference
SELECT * FROM SchemaName.TableName;

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

8 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

EXECUTE database1.schema1.proc1
EXECUTE adventureWorks2008r2_test.schema1.proc1

Example Test SQL with Automatic Fix

EXECUTE database1.schema1.proc1
EXECUTE schema1.proc1

Analysis Results

	Message	Line	Column
1	SA0253 : The current database is hardcoded in object reference.	2	8

Analysis Rules