Using name of a database inside a sql script for a full table name

Question

I struggled for a while with a bug, and then found out the reason for it in a database stored procedure code, which contained the old name of a database in a table name, whereas the current database name was already different. So, I'd like to ask:

Is there a situation in which using a database name as a part of a full table name (database name + schema name + table name) can be justified (provided we don't touch tables in other databases) or is it always a bad practice? How to correctly use a database name in sql scripts to keep code neutral to a specific database?

Code just for an illustration:

CREATE PROCEDURE [dbo].[MyProc] 
AS
BEGIN
 DELETE FROM [MyDatabase].[dbo].[MyTable] 
END

Solution 1

No, you shouldn't use database names in a stored procedure unless you need to address two databases.

It causes exactly the kinds of bugs you're seeing. When the database name changes, all your SP code breaks, or continues working, but on the old database.

It does make sense if you are sending a SQL query to the database, but only if the application dynamically picks the database name to insert into the query.

My suggestion is that you do a full export of your database schema, and search for database names that are hardcoded and remove them.

Solution 2

It really depends on how your scripts are implemented. Even if you don't refer to a table as

[MyDatabase].[dbo].[MyTable]

you will still need to refer to the database by:

USE [MyDatabase]

earlier in the script.