Extract stored proc script

• Rootman

  SSCrazy

  Points: 2641

  More actions

  April 15, 2008 at 2:55 am

  #181400

  Hi,

  Does anyone know if it is possible to extract a stored procedure script from say syscomments? I want to script up a stored proc from a database, and then run into a second db, programatically.

  Many thanks

• shamshudheen

  SSCrazy

  Points: 2390

  More actions

  April 15, 2008 at 4:55 am

  #802440

  you mean all procedures in the database

  if single then try this

  sp_helptext 'procedure name'

• Rootman

  SSCrazy

  Points: 2641

  More actions

  April 15, 2008 at 5:05 am

  #802443

  [font="Arial"]Sorry if I'm being a numpty, but do you mean something like this (although, not exactly this, as it doesn't work!):

  DECLARE @sql VARCHAR(4000)

  SET @sql = sp_helptext 'AddRecord'

  EXECUTE Scratch.dbo.sp_executesql @sql

  How do I get the returned sp into my variable?

  [/font]

• Rootman

  SSCrazy

  Points: 2641

  More actions

  April 15, 2008 at 5:23 am

  #802448

  Rummaging about in the other forums, I found this solution, posted by GilaMonster (thanks Gail).

  It works perfectly

  USE Scratch

  GO

  Select name, definition

  FROM Test.sys.sql_modules sm inner join Test.sys.objects o on sm.object_id = o.object_id

  where name = 'AddRecord'

  IT works perfectly here:

  DECLARE @sql NVARCHAR(4000)

  SELECT @sql = definition

  FROM Test.sys.sql_modules sm inner join Test.sys.objects o on sm.object_id = o.object_id

  where name = 'AddRecord'

  Execute Scratch.dbo.sp_executesql @sql

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 15, 2008 at 5:32 am

  #802454

  Pleasure.

  Just declare your variable as nvarchar(max), otherwise you risk truncation.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass