Post reply

Is there a quick way to list the contents of stored procedures?

  • May 13, 2010 at 5:24 pm

    #99231

    We have a SQL Server 2005 database with several stored procedures. Those stored procedures contain T-SQL statements. Is there a sys table somewhere that houses the T-SQL statements for all of the stored procedures within a database in one place?

  • May 13, 2010 at 5:50 pm

    #1167256

    I usually use syscomments for this - as long as the SP is not encrypted

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • May 13, 2010 at 6:01 pm

    #1167262

    That worked, thanks!

  • May 13, 2010 at 6:08 pm

    #1167263

    You might want to look at sys.all_sql_modules... it's the replacement for syscomments

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 13, 2010 at 6:26 pm

    #1167265

    Thanks again!

  • May 13, 2010 at 6:43 pm

    #1167267

    Here is another alternative as well.

    Select object_name(a2.object_id) as ModuleName,(SELECT definition AS [processing-instruction(definition)]

    FROM sys.all_sql_modules a1

    Where a1.object_id = a2.object_id

    FOR XML PATH(''), TYPE

    ) as ModuleText

    From sys.all_sql_modules a2

    Where ObjectProperty(a2.object_id,'IsMsShipped') = 0

    Order By ProcName

    This will make it so you can just click on the definition and have it formatted and easy to read. This also eliminates any MS Shipped procs,dmvs or functions present in the database.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 17, 2010 at 12:32 am

    #1168117

    sys.all_sql_modules is the better choice. As in syscomments, the statements are broken into pieces because of the 4000 characters limitation. The have to be checked and ordered against colid in the table. sys.all_sql_modules contains one record for one object.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

    • Viewing 7 posts - 1 through 6 (of 6 total)

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