Extracting Stored Procedure Content via SQL

Some nifty SQL statements I made last week:

Firstly, listing all databases on a server.

-- Get Databases
SELECT name FROM master.dbo.sysdatabases ORDER BY name

Secondly, a way to get all the user-created stored procedures from a database.

-- Get Stored Procedures
--    Type = 'P' --> Stored Procedure.
--    Category = 0 --> User Created.
SELECT * FROM sysobjects WHERE type = 'P' AND category = 0 ORDER BY name

Then we can retrieve the content of the stored procedure with the following query. {0} is the procedure name, filled in from C# with String.Format.

-- Get Stored Procedure Content
--    Name = Stored Procedure Name.
--    Colid = Multiple lines, their sequence.
SELECT text
FROM syscomments
WHERE id = (SELECT id FROM sysobjects WHERE name = '{0}')
ORDER BY colid

In C# you could concatenate the returned records to get the full stored procedure content.

However, do mind the encrypted column in syscomments. The above queries work fine when this is 0. So you might want to add some checking, and in case it’s encrypted you could first decrypt it and then display it. But you’ll have to figure out how to do that yourself :)

Update: since SQL Server 2005 the catalog views sys.databases, sys.procedures and sys.sql_modules (or OBJECT_DEFINITION()) replace these system tables and return the full procedure text in one row.