Scripting out all Stored Procedures

Question

Post reply

Scripting out all Stored Procedures

Mark Harley

SSCrazy

Points: 2259
More actions
March 26, 2009 at 6:52 am

#201598

Is there something I can run to get SQL Server to script out all of the stored procedures, functions and views for a database? If not, are there tools that do this sort of thing (that don't require coding, and ideally are free)?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

sql server management studio has that ability built in(right click on database...Tasks...Generate Scripts,

Also since you can sp_helptext any proc/view/function, you could do something like this as well:

SET NOCOUNT ON

CREATE TABLE #MyObjectHierarchy

(

HID int identity(1,1) not null primary key,

ObjectId int,

TYPE int,OBJECTTYPE AS CASE

WHEN TYPE = 1 THEN 'FUNCTION'

WHEN TYPE = 4 THEN 'VIEW'

WHEN TYPE = 8 THEN 'TABLE'

WHEN TYPE = 16 THEN 'PROCEDURE'

WHEN TYPE =128 THEN 'RULE'

ELSE ''

END,

ONAME varchar(255),

OOWNER varchar(255),

SEQ int

)

--our results table

CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )

--our list of objects in dependancy order

INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)

EXEC sp_msdependencies @intrans = 1

Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)

--synonyns are object type 1 Function?!?!...gotta remove them

DELETE FROM #MyObjectHierarchy WHERE objectid in(

SELECT [object_id] FROM sys.synonyms UNION ALL

SELECT [object_id] FROM master.sys.synonyms)

DECLARE

@schemaname varchar(255),

@objname varchar(255),

@objecttype varchar(20),

@FullObjectName varchar(510)

DECLARE cur1 CURSOR FOR

SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID

OPEN cur1

FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype

WHILE @@fetch_status <> -1

BEGIN

SET @FullObjectName = @schemaname + '.' + @objname

IF @objecttype IN('VIEW','FUNCTION','PROCEDURE')--it's a FUNCTION/PROC/VIEW

BEGIN

--CREATE PROC/FUN/VIEW object needs a GO statement

INSERT INTO #Results(ResultsText)

SELECT 'GO'

INSERT INTO #Results(ResultsText)

EXEC sp_helptext @FullObjectName

END

FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype

END

CLOSE cur1

DEALLOCATE cur1

SELECT ResultsText FROM #Results ORDER BY ResultsID

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!

Jerry Hung SSChampion Points: 12968 More actions · Answer 2

I normally use SSMS as well

Came across this free DBFriend tool on CodePlex yesterday

It generates a VS2008 project, containing all Tables/SP/Views etc... for a DB

http://dbfriend.codeplex.com/

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Mark Harley SSCrazy Points: 2259 More actions · Answer 3

Mark Harley

SSCrazy

Points: 2259

March 26, 2009 at 8:31 am

#966714

Excellent! Thanks for all the help.

brettveenstra SSC Veteran Points: 232 More actions · Answer 4

Thanks for the mention on DbFriend!

I'm going to be moving this project over to Google Code since the subversion support is native, and ultimately, DbFriend should become a database-agnostic tool.

The new repository is here: http://code.google.com/p/dbfriend