scripting all stored procedure through T-SQL

Question

Post reply

scripting all stored procedure through T-SQL

pmadhavapeddi22

SSCertifiable

Points: 5306
More actions
February 21, 2012 at 2:28 am

#256197

Hi
I need to generate script to all stored procedures in a particular DB
not using enterprise management studio
I tried looping sp_helptext for every procedure passing sp name as parameter but some how it is not dividing the lines properly and hence
i am getting syntax error
I tried the below query but formatting is totally missing
SELECT SM.Definition
FROM SYS.SQL_Modules As SM INNER JOIN SYS.Objects As Obj
ON SM.Object_ID = Obj.Object_ID WHERE Obj.Type = 'P'
Please give me any other solution
Thanks in advance

Viewing 8 posts - 1 through 8 (of 8 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

You need to separate create proc statements with batch-end "GO"

Switch query output to text and execute:

select Definition

from

(

SELECT SM.Object_ID o, 1 ord, SM.Definition

FROM SYS.SQL_Modules As SM INNER JOIN SYS.Objects As Obj

ON SM.Object_ID = Obj.Object_ID WHERE Obj.Type = 'P'

union all

SELECT SM.Object_ID o, 2 ord, 'GO'

FROM SYS.SQL_Modules As SM INNER JOIN SYS.Objects As Obj

ON SM.Object_ID = Obj.Object_ID WHERE Obj.Type = 'P'

) a

order by o,ord

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 2

The problem is if the sp has any comments with "--"

it is commenting the entire sp, thats why I need an exact output like

the way we generate script using management studio.

when I used sp_helptext in a stored procedure, I got the result

the way I wanted, but it is failing for huge line procedures

I hope you got my point

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Have you switched the output into Text?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Ant-Green SSC Guru Points: 113544 More actions · Answer 4

Does it have to be done in T-SQL?

You could right a few lines of code in powershell which will loop through all the procedures and dump it out to a text file, just a different approch to the same solution.

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 5

Yes I did but it is not able to handle " /* " which makes the script into green lines (takes a comment) and it is making the entire script into green lines

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

can you please post a stored proc definition which causes problem. I've run the query for procs which contain /* comments and it looking just fine.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 7

Hey....

I got the solution...

I have reused sp_helptext and renamed it and modified

the length of the column width

I think it is working fine now.

Thanks for the help

scripting all stored procedure through T-SQL

Cookies on SQLServerCentral