Post reply

How to replace the database hardcoding?

  • November 6, 2012 at 9:43 pm

    #270554

    Hi friends I have database 'A' in my Prod and Testing environments. Now I have created a new database 'B' in the testing environment and restored the database 'A' from Prod to this 'B'.

    Now the issue is in Database 'A' I have hardcoded "Use A" in many places and also I have use A.dbo.table names etc inside the storedprocedures and functions.

    So at the moment what I'm doing is after restoring the database I use wizard to generate script for each object types and then do a "Find - Replace" .

    Is there any other way so in one go I can replace all the reference to database "A" to the new database "B"..?

    Thanks & Regards,
    MC

  • November 7, 2012 at 12:48 am

    #1556653

    So from this I take it that Database B has a completely different name to Database A,, eg Database A name was Test_Live, and now its called Test_Dev, and references to objects in the database are something like

    Create Proc aProc

    AS

    Select col1 from Test_Live.Schema.Table

    I'm not sure why you would specify the database to reference internal objects, unless its part of some specific inhouse coding standard, which to me seems a little anacronystic and rigid.

    If this is the case then you might benefit from using synonyms and then reference them inthe code instead of the instead of specifing the Database

    That way you only have to drop and recreate the Synonyms, in this db which can easily be done with an SQLCMD script and Variable

    :SetVar DatabaseName "myDb"

    USE [$(DatabaseName)]

    GO

    Drop Synonym MySynonym

    GO

    CREATE Synonym MySynonym FOR [$(DatabaseName)].Schema.MyTable

    Either way is a major refactor of the code as there is no simple or safe way to do this that I can think off off the top of my head.

    In Terms of the USE statement, I take it you do this for 'ad-hoc' scripts in SSMS, in whcih case you could run these under SQLCMD mode in SSMS, and use a variable to do the switch, as per the above methiod just replacing the Database portion of the name with [$(DatabaseName)] in the script.

    Edit : SQL vars not correctly used or declared.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply