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

4 Comments

  • Heh, lol...

    It has been ages since I wrote any SQL... yesterday I wanted to do an INSERT; couldn't remember the syntax!

    Damn those frameworks :p

  • I got that too after using Gentle for a year and doing everything O/R-mapping :)

  • When the content of SP is larger than 4000 chars I can't get all the body of my SP.

    How can we solve this problem?

    regards,

    ansari

  • While extracting stored procedure using the following statements
    SELECT text
    FROM syscomments
    WHERE id = (SELECT id FROM sysobjects WHERE name = '{0}')
    ORDER BY colid

    we get the code but some code is missing.
    Don't know why?
    Please help?

Comments have been disabled for this content.