SYNTAX help on getting the STORED PROC Definition

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 5, 2016 at 11:15 am

  #323363

  Folks

  I know this can be done but I am not sure what system tables/views need to be accessed.

  I want to get the stored proc name and stored proc definition in the form of a SQL statement

  Select sp_name , sp_definition FROM ........

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 5, 2016 at 11:24 am

  #1849622

  mw112009 (1/5/2016)

  ---

  Folks

  I know this can be done but I am not sure what system tables/views need to be accessed.

  I want to get the stored proc name and stored proc definition in the form of a SQL statement

  Select sp_name , sp_definition FROM ........

  For procedures, you can use this:

  SELECT p.name, m.definition

  FROM sys.procedures p

  JOIN sys.sql_modules m ON p.object_id = m.object_id

  For other objects, you can change the sys.procedures view to sys.objects.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  January 5, 2016 at 11:39 am

  #1849627

  Piling on, here is the short disco version

  😎

  SELECT OBJECT_DEFINITION(OBJECT_ID(N'[schema_name].[object_name]'));

• johnwalker10

  SSCrazy Eights

  Points: 9124

  More actions

  January 5, 2016 at 10:01 pm

  #1849739

  USE AdventureWorks;

  GO

  SELECT definition

  FROM sys.sql_modules

  WHERE object_id = (OBJECT_ID(N'AdventureWorks.dbo.uspLogError'));