C#/SQL Get all Stored Procedures and their Code

Question

How can I obtain all stored procedures and their code from SQL Server?

I need to show data that is similar to what SQL Server Management Studio shows, incl. allowing to browse through SP's with code, tables and indexes.

Any hints on how to get this information out of SQL Server? P.S., I'm using C#.

Answer 1

SELECT sysobjects.name, syscomments.text 
FROM sysobjects 
JOIN syscomments 
 ON sysobjects.id = syscomments.id
WHERE xtype='P'

Answer 2

SQL Server Management Objects (SMO) would be a good place to start. This article has code examples including listing Stored Procedures:

Getting started with SQL Server Management Objects (SMO)

Answer 3

Look at the Information_schema.procedures view.

Answer 4

Others have pointed you in the right directions. It can also be helpful to know about the OBJECT_DEFINITION() function.

Answer 5

The use the the "sysobjects" catalog view is discouraged as of SQL Server 2005 - instead, you should start using the catalog views from the "sys" schema (or the INFORMATION_SCHEMA views, if database-portability is important to you) like this:

SELECT
    pr.name,
    m.definition,
    pr.type_desc,
    pr.create_date,
    pr.modify_date 
FROM 
    sys.procedures pr
INNER JOIN 
    sys.sql_modules m ON pr.object_id = m.object_id
WHERE 
    is_ms_shipped = 0
ORDER BY 
    pr.Name

Marc