I have 2 seperate SQL databases on the same SQL instance. Database1 references tables on Database2 in its views and stored procedures. Everything was working fine until we had to create a fresh version of Database2 and rename the current Database2 to Database3.
The DBA did the following:
Took Database2 offline, then reattached it as Database3. Database2 was then deleted from SQL.
A new version of Database2 was created.
The view and stored procedures in Database1 are still referencing the tables on Database3 unless we add the server name to the FROM clause in any queries.
The following view on Database1 goes to Database3 in error:
select * from Database2.dbo.Projects
For the correct behaviour we specify the server name:
select * from ProductionDB.Database2.dbo.Projects
Why are the views and stored procs of Database1 still referencing Database3 (unless we specify server name)?
Is it because we renamed the database by re-attaching instead of backup and restore?
Are there internal references in Database1 that need updating?
Are there hidden database synonyms I can't find?
sql sql-server backup database-administration cross