How to drop and recreate all procedures in a database

  • I am upgrading some databases to sql 2005.

    My goal is to drop every stored procedure and recreate them, to make sure that they will not fail when changing the compatibility level.

    I created a table, and inserted the result of

    sp_helptext [procedurename] onto it

    then i copied the result into management studio.

    The problem:

    the text gets inserted into my temp table in rows, splitting up words, so I have to manually fix the procedure, and put words toguether before I can execute the statements.

    Is there a way to make this task a little more simple? so that once i have the text of the procedure I can just run it?

    Here is my query:

    create table #temp (ProcText varchar (max))

    DECLARE @ProcName varchar(128)

    DECLARE @PROCTEXT VARCHAR(MAX)

    SET NOCOUNT ON

    DECLARE tnames_cursor CURSOR FOR

    SELECT name FROM sys.objects

    WHERE type = 'P'

    ORDER BY name

    OPEN tnames_cursor

    FETCH NEXT FROM tnames_cursor INTO @ProcName

    WHILE (@@fetch_status = 0)

    BEGIN

    insert into #temp

    exec SP_HELPTEXT @ProcName

    insert into #temp values ('go')

    exec ('drop proc ' + @ProcName)

    FETCH NEXT FROM tnames_cursor INTO @ProcName

    END

    CLOSE tnames_cursor

    DEALLOCATE tnames_cursor

    GO

    select * from #TEMP

  • You could just use management studio to generate the scripts for you...right click on the database in object explorer, and go to Tasks->Generate Scripts. The wizard will ask you what type(s) and which specific objects you want to script.

  • Sorry, forgot something. In order to generate the DROP part of it, there is a "Script drop" option that you will want to set to true on the "Choose script options" page.

  • How about triggers. Looks like I would have to script the tablein order todo them as well?

    Is there a way to just do the triggers?

  • Ohh triggers complicate things 🙂

    Unfortunately, there is not a way to do just triggers using that wizard. I have no idea why they put that limitation into the tool.

    If you are doing this after the database is on SQL Server 2005, you can query the sys.sql_modules catalog view, which stores the T-SQL in the definition column, which is nvarchar(max) instead of nvarchar(4000) like it was in the syscomments table in SQL 2000, so the whole definition should be in that one row. The problem you will have with that method is that Management Studio does not return new lines properly in a grid, and the default options for the results to text truncates it. I am trying to find out where/if you can change that. I'll let you know if I figure that out.

  • Ahh, easy I was just looking in the wrong options window. Go to Tools->Options and then Query Results->SQL Server->Results to Text and set the "Maximum number of characters displayed in each column" option to something larger than the default. It seems to have a max of 8192 though...so hopefully you don't have really massive triggers so this actually helps....

  • thank you so much!!

  • Ack... don't drop them... then you have to worry about all sorts of things including grants, etc, etc.

    Generate ALTER statements for them all.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • True, but the generate script utility gives you an option to script object permissions as well so that is not a big deal. Regardless, you can use that script as the starting point and replace the CREATE statements with ALTER statements...just make sure you only replace CREATE PROCEDURE statements, and not any other CREATEs that may be in your procedure code.

  • Dropping the procs will be a very big deal if there are any dependencies... the script generator isn't real good a recognizing those 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Honestly, the Upgrade Advisor should check some of this stuff. I agree with Jeff, I wouldn't drop them. Could cause lots of issues.

    I'd develop a plan to execute them and see if they all work in 2005. Are we talking about so many procedures?

  • wouldn't it just be easier to attach a backup copy of the DB in a new 2005 instance and try to see if or how they work? Most of the challenges I saw were in HOW the stored procs worked after you upgraded the DB, not in whether it would get past the compiler. And again - most of it was in performance (speed, efficiency, blocking, etc..) differences, and not so much in actual changes in behavior outright.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thank you so much for your advice! I have several databases, some of them with over 300 procedures, so runing them all will be difficult. I will look at the upgrade advisor.

  • I am attaching a zip file with a proc that will script out all your tables, including the triggers on the tables. If you just want triggers you can easily change it as required.

    The other 2 procs will script out all your stored procs and all your functions.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Situations like this are where unit testing is a great thing. I'd suggest creating a test suite for your database code going forward...probably not feasible to do it for your existing code, but as you make modifications you can add them piece by piece. If you can get your hands on some automation tools, even better.

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

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