SQL Server Performance

Find & Replace DatabaseName in all DB OBJECTS

Discussion in 'ALL SQL SERVER QUESTIONS' started by bsethi24, Mar 12, 2012.

  1. bsethi24 New Member

    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
  2. Hrishi_nk New Member

    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
  3. FrankKalis Moderator

    I would probably just simply script out all objects, then use a text editor to search and replace, copy then result back into SSMS and execute it. And instead of using the INFORMATION_SCHEMA views, you might want to have a look at sys.sql_modules.

Share This Page