Extracting stored procedure content via SQL

Posted Wednesday, October 18, 2006 9:46 PM by CumpsD

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

Comments

# re: Extracting stored procedure content via SQL

Thursday, October 19, 2006 3:36 AM by BertG

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

# re: Extracting stored procedure content via SQL

Thursday, October 19, 2006 3:44 AM by CumpsD

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

# re: Extracting stored procedure content via SQL

Thursday, June 28, 2007 10:12 AM by H.Ansari

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

# re: Extracting stored procedure content via SQL

Saturday, August 18, 2007 5:47 AM by Anil

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?