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?

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.