Post reply

scripting all stored procedure through T-SQL

  • 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

  • February 21, 2012 at 2:46 am

    #1449534

    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]

  • February 21, 2012 at 3:05 am

    #1449540

    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

    #1449542

    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]

  • February 21, 2012 at 3:26 am

    #1449546

    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

    #1449550

    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

    #1449568

    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]

  • February 21, 2012 at 6:29 am

    #1449663

    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 7 (of 7 total)

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