How Do I Get The Content Of A Sql Stored Procedure In Powershell?

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 n

Solution 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.

Solution 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