1

After a transfer of an SP from one schema to another, the information_schema.routines.routine_definition and object_definition() still show the original schema.

create schema SchemaA;
go
create proc SchemaA.Proc1 as begin   return 1;  /*jjj*/  end
go
create schema SchemaB;
go
alter schema SchemaB transfer SchemaA.Proc1;
go

select specific_catalog, Routine_schema, routine_type, Routine_definition 
from information_schema.routines where Routine_definition like '%jjj%'

exec sp_recompile @objname = 'SchemaB.Proc1'; -- mark for recompile

exec SchemaB.Proc1; -- run it

-- Still wrong
select specific_catalog, Routine_schema, routine_type, Routine_definition 
from information_schema.routines where Routine_definition like '%jjj%'

-- Also wrong
select object_definition(object_id('Sandbox.SchemaB.Proc1'))  

Manually re-compiling it fixes the issue and we do have a moderately scary script to do that for all SPs but is there any other way to rebuild the information_schema tables?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.