Recompiling SPs, Views, Triggers and User Defined Functions

The last step in the NewYear process is renaming database cetgroups3 to cetgroups3_old. After setting cetgroups3_old to OFFLINE we saw process failing trying to access database objects using cross database references with three-part notation (for example: select ... from cetgroups3.dbo.Countries) This was resolved by recompiling the SPs that were failing.

This behavior is described in the following links:

• https://learn.microsoft.com/en-us/answers/questions/952454/effect-on-renaming-the-database-log-and-datafiles
• https://dba.stackexchange.com/questions/41671/how-do-i-refresh-sql-server-cross-database-references-after-a-database-is-reatta

To recompile all code doing cross database reference to cetgroups3 objects we need to run the script below. It will produce a set of commands that will recompile the relevant code.

DROP TABLE #SPs
GO

CREATE TABLE #SPs 
(
  db_name varchar(100), 
  object_name varchar(100), 
  object_description varchar(100), 
  text nvarchar(max)
);
GO

EXEC sp_msforeachdb 'USE [?]; 
	INSERT INTO #SPs select ''?'', s.name +''.'' + o.name as object_name, o.type_desc, m.definition
	FROM sys.sql_modules m
	INNER JOIN sys.objects o on m.object_id = o.object_id
	INNER JOIN sys.schemas s on s.schema_id = o.schema_id
	ORDER BY o.name
	';
GO

-- SPs
SELECT 'USE [' + db_name + ']; EXEC sp_recompile ''' + object_name + ''';' FROM #SPs where text like '%cetgroups3.%' and object_description != 'VIEW' order by 1;
GO

-- VIEWs
SELECT 'USE [' + db_name + ']; EXEC sp_refreshview ''' + object_name + ''';' FROM #SPs where text like '%cetgroups3.%' and object_description = 'VIEW' order by 1;
GO

Run the output of the last 2 select statements.