Find & Replace DatabaseName in all DB OBJECTS

Dear All,

Hi! I have a DB which has many tables, views, SPs, Functions, Triggers etc. Now, In many of them I have used fully qualified name (i.e. DBNAME.DBO.ObjectName).

If I restore this db on any other server with some other name then the OBJECTS having fully qualified name will show me errors.

So, how can I find the objects those have DBName & replace the DBName with the NEW DBName?

For Example,

We have a DB named "TEST" & in more than 150 SPs used the tables with fully qualified name pattern (TEST.DBO.M_Item).

I took the backup of this DB & restored it on another server by name "EXAM".

Now, First I need to Find how many objects have "TEST.DBO." & then replace "TEST.DBO." with "EXAM.DBO.".

How can I achieve the same?

Thanks & Regards,
BSethi24

Hi in order to find out which procedures or functions contains the 'TEST.dbo' you can use below queries.

SELECT routine_name,routine_type FROM INFORMATION_SCHEMA.routines WHERE ROUTINE_DEFINITION like '%TEST.dbo%'
OR
SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE TEXT LIKE '%test.dbo%'

but you will have to manually alter the procedures and functions