Sql-server – How to refresh SQL server cross database references after a database is reattached with a different name? (Are there hidden synonyms?)

backupsql server

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:

  1. Took Database2 offline, then reattached it as Database3. Database2 was then deleted from SQL.

  2. A new version of Database2 was created.

The problem:

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

Best Answer

You might want to run sp_refreshview, it is recommended to run this if the underlying tables change at all (this case seems to be a good candidate for that case).

I found the underlying cause. The parse trees of views are generated and cached:

select * from sys.syscacheobjects
where objtype = 'View'

This is the output of the tokenizer, so an attach detach should invalidate cached tokens. An sp_refreshview rebuilds the parse tree.

From the documentation for sp_refreshview:

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Related Question