February 21, 2012 at 2:28 am
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
February 21, 2012 at 2:46 am
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
February 21, 2012 at 3:05 am
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
February 21, 2012 at 3:13 am
Have you switched the output into Text?
February 21, 2012 at 3:26 am
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.
February 21, 2012 at 3:30 am
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
February 21, 2012 at 3:57 am
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.
February 21, 2012 at 6:29 am
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy