How do I get the content of a SQL Stored Procedure in Powershell?

Question

I'm perverted in the sense that I hate exploring code and database structures in a tree view and I'd much prefer using something like the Powershell for that. Most of the stuff I need to do in SQL is exploring, i.e. looking at what columns does a table have or what does a particular stored procedure do.

Looking at table columns is as easy as ls'ing the columns directory of a table, but how would I get the contents of a stored procedure?

Answer 1

If you want to run this on Sql Server 2008 then here is a Cmdlet that will help you with it.

If you are using Sql Server 2005 then here is a page with a script to help you with this.

[EDIT] You may use the SP sp_helptext to see the contents of the required stored procedure.

Answer 2

They text of a sproc lives in a data dictionary table sys.sql_modules. As an aside, this Stackoverflow post has a data dictionary reverse engineering script that (amongst other things) gets the text of view definitions from this table - reverse engineering sproc definitions works much the same.

A minimal script to retrieve the progam text of a stored procedure would look like:

select m.definition
  from sys.objects o
  join sys.sql_modules m
    on o.object_id = m.object_id
  join sys.schemas s
    on s.schema_id = o.schema_id
 where s.name = 'foo'   -- Schema name
   and o.name = 'bar'   -- Sproc name