Extracting stored procedure content via SQL

  • Posted on 2006-10-17 in .
  • (estimated time, 1 min)

Some nifty SQL statements I made last week:

Firstly, listing all databases on a server.

[sql] -- Get Databases SELECT name FROM master.dbo.sysdatabases ORDER BY name [/sql]

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

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

Then we can retrieve the content of the stored procedure with the following query:

[sql]

  • 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 [/sql]

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

Search

Categories