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

Question

Post reply

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

imani_technology

SSCrazy Eights

Points: 8200
More actions
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?
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]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

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

MM

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

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 2

imani_technology

SSCrazy Eights

Points: 8200

May 13, 2010 at 6:01 pm

#1167262

That worked, thanks!

WayneS SSC Guru Points: 95460 More actions · Answer 3

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

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 4

imani_technology

SSCrazy Eights

Points: 8200

May 13, 2010 at 6:26 pm

#1167265

Thanks again!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 6

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]