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:
-- 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 :)