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

Gustav Bertram · Accepted Answer · 2011-11-07 10:56:19Z

3

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.

answered Nov 7, 2011 at 10:56

Gustav Bertram

15k3 gold badges46 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ChrisBD · Accepted Answer · 2011-11-07 10:42:50Z

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.

answered Nov 7, 2011 at 10:42

ChrisBD

9,2393 gold badges25 silver badges36 bronze badges

Comments

dani herrera · Accepted Answer · 2011-11-07 10:49:21Z

0

It is possible to mix trusted database tables in a single query. When someone do this,it is justified and mandatory to include database on table 'path'.

I don't found a reason out of this scenario if stored procedure and table is on the same database.

You can search all database name occurencies through database catalog in order to fix your development. For SQL Server 2005:

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%databasename%'
GO

For SQL Server 2000:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%databasename%'
GO

answered Nov 7, 2011 at 10:49

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related