How to drop and recreate all procedures in a database

Question

How to drop and recreate all procedures in a database

sandra pastrana

SSC Eights!

Points: 979
More actions
December 11, 2007 at 10:18 am

#177499

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

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

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

Larry Taylor Old Hand Points: 326 More actions · Answer 1

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.

Larry Taylor Old Hand Points: 326 More actions · Answer 2

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.

sandra pastrana SSC Eights! Points: 979 More actions · Answer 3

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?

Larry Taylor Old Hand Points: 326 More actions · Answer 4

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.

Larry Taylor Old Hand Points: 326 More actions · Answer 5

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....

sandra pastrana SSC Eights! Points: 979 More actions · Answer 6

sandra pastrana

SSC Eights!

Points: 979

December 11, 2007 at 2:24 pm

#759187

thank you so much!!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

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)

Larry Taylor Old Hand Points: 326 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

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)

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 10

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?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

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?

sandra pastrana SSC Eights! Points: 979 More actions · Answer 12

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.

EdVassie SSC Guru Points: 60472 More actions · Answer 13

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

Larry Taylor Old Hand Points: 326 More actions · Answer 14

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.