Post reply

Is there a way to programatically alter a function

  • July 30, 2009 at 8:39 am

    #72398

    Hi Guys

    If i want to run alter function on the definition of all functions on the database. Is there a way to programatically do this rather then opening them one after the other and running alter statement ?

  • July 30, 2009 at 9:01 am

    #1032437

    it depends on what you are doing Dean;

    offhand, if it was a simple find and replace, you could do something like this:

    declare

    @sql varchar(max)

    declare c1 cursor for

    SELECT ROUTINE_DEFINITION

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_TYPE='FUNCTION'

    --AND ROUTINE_DEFINITION LIKE '%OLDTABLE%' --???

    open c1

    fetch next from c1 into @sql

    While @@fetch_status -1

    begin

    --change from CREATE FUNCTION TO ALTER FUNCTION

    SET @sql=REPLACE(@sql,'CREATE FUNCTION','ALTER FUNCTION')

    --change some old value to a new value

    SET @sql=REPLACE(@sql,'OLDTABLE','NEWTABLE')

    --print @sql

    exec(@sql)

    fetch next from c1 into @sql

    end

    close c1

    deallocate c1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 30, 2009 at 9:23 am

    #1032461

    Thanks for the reply.

    The plan is as follows, run a script that will order all objects according to dependency orderings, based on this in the form of a cursor. Run a script that will programatically run alter statements in the order of dependency. This way, the database is guaranteed to be properly ordered dependency wise.

  • July 30, 2009 at 9:25 am

    #1032464

    i saw your other thread on dependancy order...what does that have to do with altering your functions? i'm confused on that....

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 30, 2009 at 1:50 pm

    #1032650

    What I have found is that if functions are not created in the right order, they can break replication. As articles are sent down to subscribers in the wrong order, so when it tries to re-create the schema, it fails.

  • July 30, 2009 at 2:33 pm

    #1032682

    ahh, i got you...so you are simply altering them with identical code so the dependencies get entered in correctly, right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 30, 2009 at 3:43 pm

    #1032724

    Thats very right.

  • August 4, 2009 at 6:50 am

    #1034205

    Any work arounds guys ?

  • August 4, 2009 at 8:31 am

    #1034286

    the cursor i posted works, did you try that yet? just remove the oldtable/newtable replace function.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 9 posts - 1 through 8 (of 8 total)

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