Tuesday, April 10, 2007

Script Multiple Procs With Create And Drop Statements In SQL Server 2005

In SQL Server 2005 Management studio you can not script out multiple procedures with a create and drop statement
This is what you have to do:
1. Open you favorite database in the management studio.
2. Make sure the summary window is visible. (Press F7)
3. In the summary window, double click databases.
4. Double click 'YourDatabase'
5. Double Click 'Programability'
6. Double Click 'Stored Procedures'
7. Select the stored procedures you want to script. (using CTRL/SHIFT clicking).
8. Right click -> Script Stored Procedures As -> Drop -> New Query Editor Window.

Repeat step 8 for the Create.

Who wants to do that? Not me.
Here is a pure T-SQL solution, I didn't feel like using SMO. I can just run this code, copy and paste the output in a new window and I am done.

This code takes care of schema's it also grabs just procedures and only the ones that are created by users (OBJECTPROPERTY 'IsMSShipped' takes care of that)



USE AdventureWorks
GO

SET NOCOUNT ON

CREATE TABLE
#ProcHolder (ID int identity,ProcName VARCHAR(8000), ProcDefinition VARCHAR(MAX))

INSERT #ProcHolder
SELECT QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME),OBJECT_DEFINITION(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)),'IsMSShipped') = 0
--SELECT * FROM #ProcHolder --if you want to test


DECLARE @LoopID int,@MaxLoopID int,@ProcDefinition VARCHAR(MAX),@ProcName VARCHAR(8000)
SELECT @LoopID =1,@MaxLoopID = MAX(id) FROM #ProcHolder

WHILE @LoopID < @MaxLoopID
BEGIN
SELECT
@ProcDefinition = ProcDefinition,@ProcName =ProcName
FROM #ProcHolder
WHERE id = @LoopID

PRINT 'if exists (select * from dbo.sysobjects where id = OBJECT_ID(N''' + @ProcName + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure ' + @ProcName
PRINT 'GO'
PRINT ''
PRINT ''

PRINT @ProcDefinition
PRINT 'GO'
PRINT ''
PRINT ''

SET @LoopID = @LoopID +1
END



DROP TABLE #ProcHolder


That is it, let me know what you think

3 comments:

Paul C Smith said...

Useful script there, thanks for sharing it!

I thought I'd contribute my own modifications if anybody wants to use them.

For mere convenience, I added a simple GRANT EXECUTE... block to be printed right after the procedure definition.

Also, I have a lot of looonnnnng procedures in my DB, so to get around SQL Server Management Studio's 8,000 character per-column per-print-statement limit, I reworked the printing loop a bit. The gist of it is that when the object definition is longer than a user-defined threshold, it gets split into multiple chunks, all of which break on a CR or LF.

The resulting output seems pretty solid / accurate in my own use, even though the whitespace can be a bit ugly in places.

Anyway, here goes; hope the formatting doesn't blow up too badly (Blogger has a habit of stripping the end quotation mark from some long string literals):


SET NOCOUNT ON
CREATE TABLE #ProcHolder (ID int identity,ProcName VARCHAR(MAX), ProcDefinition VARCHAR(MAX))

INSERT #ProcHolder
(ProcName, ProcDefinition)
SELECT
QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME),
OBJECT_DEFINITION(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)),'IsMSShipped') = 0
--SELECT * FROM #ProcHolder --if you want to test
DECLARE
@LoopID int,
@MaxLoopID int,
@ProcDefinition VARCHAR(MAX),
@ProcName VARCHAR(1024),
@ProcLoop int
SELECT
@LoopID =1,
@MaxLoopID = MAX(id)
FROM #ProcHolder

WHILE @LoopID < @MaxLoopID BEGIN
SELECT
@ProcDefinition = ProcDefinition + ' ',
@ProcName = ProcName
FROM #ProcHolder
WHERE id = @LoopID
-- Write out the If / Drop / Create header block:
PRINT 'if exists (select * from dbo.sysobjects where id = OBJECT_ID(N''' + @ProcName + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure ' + @ProcName
PRINT 'GO'
-- If @ProcLen >= @MaxChunkSize, print in chunks:
DECLARE
@MaxChunkSize int,
@ProcLen int
SELECT
@MaxChunkSize = 2000,
@ProcLen = DATALENGTH(@ProcDefinition)
FROM #ProcHolder
IF @ProcLen > @MaxChunkSize BEGIN
DECLARE
@CharsPrinted int,
@ChunkSize int,
@CurrentChunk VARCHAR(MAX)
SELECT
@CharsPrinted = 1,
@ChunkSize = @MaxChunkSize
WHILE @CharsPrinted < @ProcLen BEGIN
SET @ChunkSize = (@ProcLen - @CharsPrinted)
IF @ChunkSize > @MaxChunkSize
SET @ChunkSize = @MaxChunkSize
SET @CurrentChunk = SUBSTRING(@ProcDefinition, @CharsPrinted, @ChunkSize)
IF (@CharsPrinted + DATALENGTH(@CurrentChunk)) < @ProcLen BEGIN
WHILE RIGHT(@CurrentChunk,1) <> CHAR(10) AND RIGHT(@CurrentChunk,1) <> CHAR(13) BEGIN
SET @ChunkSize = @ChunkSize - 1
SET @CurrentChunk = SUBSTRING(@ProcDefinition, @CharsPrinted, @ChunkSize)
END
END
PRINT @CurrentChunk
SET @CharsPrinted = @CharsPrinted + DATALENGTH(@CurrentChunk)
END
SET @ChunkSize = (@ProcLen - @CharsPrinted) + 1
SET @CurrentChunk = SUBSTRING(@ProcDefinition, @CharsPrinted, @ChunkSize)
PRINT @CurrentChunk
END
ELSE BEGIN
PRINT @ProcDefinition
END
PRINT 'GO'
PRINT 'GRANT EXECUTE ON ' + @ProcName + ' TO PUBLIC'
PRINT 'GO'
PRINT '---------------------------------------------------------------------------------------------------------'
PRINT '---------------------------------------------------------------------------------------------------------'
PRINT ''
SET @LoopID = @LoopID + 1
END

DROP TABLE #ProcHolder

Anonymous said...

Playing around with the code a bit and found that if database diagrams have been added to your user Db then the specific sp's ( [dbo].[sp_upgraddiagrams]
[dbo].[sp_helpdiagrams]
[dbo].[sp_helpdiagramdefinition]
[dbo].[sp_creatediagram]
[dbo].[sp_renamediagram]
[dbo].[sp_alterdiagram]
[dbo].[sp_dropdiagram]) will show up.

Great tip!!

Anonymous said...

Hi,

Actually you can script multiple objects with drop and create to multiple files by using the built-in Generate Script Wizard in Management Studio 2005. Just right-click on the database / Tasks / Generate Scripts. Works like a charm for me

Regards,

Jimmy Karlsson