Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    13

    Question Unanswered: Modify all store proc in DB in one shot

    Is it possible that i can use a store proc to modify all the rest of my store procedures that i have in my DB ??

    I have so many created allready and it will be to long to go throught each one of them to modify my text inside.

    what i wish to do is a store proc that will allow me to loop to all my store proc of the current DB and look inside for specific text that i would like to change with the new value !!

    any advise or example..

    thanx.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My strong suggestion is to script all of the stored procedures (including their permissions) using SQL Enterprise Mangler, and save two copies of the script. Edit one of the scripts to suit your fancy, then play it into your database. Test, test, test, then test some more.

    When you discover what you mangled, play in the pristine script and start over!

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Are the procedures stored in files or do they solely exist in sqlserver? If the latter, you have perhaps a challenge. I'm not sure if this will work: select the inside from syscomments (column text), then change what you want. You could commit the change to the syscomments table but you'll have to reconfigure sql server to allow this and I am not sure if that's a good idea. Besides, you'll have to think about having to let sql server know the procedure changed so it'll recompile (in order to make sure it'll use the latest version). Instead you could try to have the contents of the procedure executed using sp_executesql, don't forget the drop and create commands though.
    I would not recommend either one since you'll have some issues to consider prior actually doing the changes.

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    btw: Pat's suggestion is way preferred over mine

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey look at that...a cross post

    SQLTeam

    Check out the link in the link...let me know what you think...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •