Post reply

My Script of database has changed field names

  • June 22, 2005 at 12:13 pm

    #87986

    Hi all,

    I generated the script for all the stored procedures in my database and

    I noticed that in a very critical stored procedure ,the script has made some changes by itself.

    The change is shown below-

    Part of Real Stored procedure in Database-

    SELECT TCN_Site=tcn, TCN_Company =tcn, TCN_Parent =tcn, parent_pointer = parent_firm_id, firm_id = firm_or_site_id,

    completed = 'ppppppppppppppppppppp' , counter = -1 --default b4 write to #complete

    into #TCNult

    FROM dev_img_oloes.dbo.tcn_firm_or_site_master

    WHERE PARENT_FIRM_ID is null

    The Same part in the script-

    SELECT TCN_Site=UCN, TCN_Company =UCN, TCN_Parent =UCN, parent_pointer = parent_firm_id, firm_id = firm_or_site_id,

    completed = 'ppppppppppppppppppppp' , counter = -1 --default b4 write to #complete

    into #TCNult

    FROM dev_img_oloes.dbo.tcn_firm_or_site_master

    WHERE PARENT_FIRM_ID is null Hi All,

    As we can see it changed TCN to UCN.

    It would be great if somebody know about it and help in solving this because this is messing up some important processes.

    Thanks.

     

  • June 22, 2005 at 12:28 pm

    #567609

    Select DISTINCT o.Name, O.XType from dbo.SysObjects O inner join dbo.SysComments C on O.id = C.id where text like '%UCN%'

  • June 22, 2005 at 12:48 pm

    #567622

    Thanks a lot for this code. Now I See that there are 2 stored procedures in the script which

    are having the same name(Which is not the case with the database of course).Thats why I was looking at that stored procedure ...The second stored

    procedure's name is changed in the script from what it is called in the database.

    What could be the possible reason for script to change the name of sproc ?

     

    Thanks.

  • June 22, 2005 at 12:51 pm

    #567626

    When you rename an object, its script create name is not modified (except for tables). You have to do it manually.

  • June 22, 2005 at 12:52 pm

    #567629

    Great !! I think my problem is solved.

    Thanks .

  • June 22, 2005 at 12:58 pm

    #567635

    HTH.

  • June 22, 2005 at 1:33 pm

    #567655

    Is there some way to find  all those stored procedures

     for which we are getting their  old names in the script.

    Thanks.

  • June 22, 2005 at 1:37 pm

    #567657

    Select DISTINCT o.Name, O.XType from dbo.SysObjects O inner join dbo.SysComments C on O.id = C.id where text not like '%' + O.name + '%'

    This isn't 100% effective though.

  • June 22, 2005 at 1:46 pm

    #567662

    this is why you find places where sp_rename is not allowed

    either use drop/create or some third party tool

     


    * Noel

  • June 22, 2005 at 2:04 pm

    #567668

    Is sp_renamedb forbiden too?

  • June 22, 2005 at 2:09 pm

    #567671

    sp_renamedb is acceptable, you usually don't have that many DB at once to rename

     


    * Noel

  • June 22, 2005 at 2:18 pm

    #567678

    Unless you really wanna piss off some dbas .

  • June 22, 2005 at 2:26 pm

    #567681

    declare @cmd varchar(200) , @i bigint

    set @i = 0

    while 1 = 1

    begin

     set @cmd = 'create database db' +cast (@i as varchar(50))

     exec (@cmd)

     set @i = @i + 1

    end

    -- now practice sp_renamedb


    * Noel

  • June 23, 2005 at 2:58 am

    #567799

    If you dont want to practise, use this

    declare @cmd varchar(200) , @i bigint

    set @i = 0

    while 1 = 1

    begin

    set @cmd = 'drop database db' +cast (@i as varchar(50))

    exec (@cmd)

    set @i = @i + 1

    end

  • June 23, 2005 at 6:53 am

    #567872

    while 1 = 1??

    This may hang for a while .

Viewing 15 posts - 1 through 14 (of 14 total)

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