Stored Procedure System Definition

• Dominic_godfrey

  SSC-Addicted

  Points: 402

  More actions

  December 17, 2013 at 8:21 am

  #287496

  Hi,

  This is a bit of a strange one, I have been given the task of syncronising two databases on separate servers which consist of a large amount of stored procedures, tables, views, funcitons etc. These two databases have been development and live environments which have become out of sync, and have been developed over constantly over the last 11 years.

  I have syn'd the databases using the stored procedure definitions from the live server to the dev server using the following code:

  SELECT

  OBJ.name AS StoredProcedureName,

  MODU.definition AS spDefinition

  FROM [ServerName].[DatabaseName].SYS.OBJECTS OBJ

  INNER JOIN [ServerName].[DatabaseName].SYS.SQL_MODULES MODU ON

  MODU.object_id = OBJ.object_id

  WHERE

  OBJ.Type = 'P'

  Here is my problem, I have some stored procedures where the defintion from this query is not the same as when scripting the stored procedure in an alter statement.

  Does anyone know why the definition in SYS.OBJECTS might be wrong and how it can be solved?

  Thanks

  Dom

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 17, 2013 at 8:42 am

  #1674106

  scripting from the GUI, whether create or alter, will contain the same definition, but the GUI adds

  this like SET ANSI_NULLS ON and other settings; potentially the USE [database] statement as well, but those all can be switched on and off for the GUI under scripting options.

  also the GUI can change the definition to be dynamic SQL if you chose an if exists option.

  In 2005 and above, I've never seen the core script of a procedure differ between ways you access it.

  is it the Settings as being different that you are talking about?

  this setting makes dynamic SQL instead of simple CREATE/ALTER:

  

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• MysteryJimbo

  SSC-Insane

  Points: 24203

  More actions

  December 18, 2013 at 1:58 am

  #1674307

  Here is my problem, I have some stored procedures where the defintion from this query is not the same as when scripting the stored procedure in an alter statement.

  Does anyone know why the definition in SYS.OBJECTS might be wrong and how it can be solved?

  Thanks

  Dom

  It could be a problem caused by using sp_rename.

  https://www.simple-talk.com/blogs/2006/05/10/a-reason-to-avoid-sp_rename/[/url]

  You could use the scripting task from within the GUI. Right click the DB -> Tasks -> Generate Scripts..

  Alternatively you could use a third party DB comparison tool like Red Gate's SQL Compare.

• Dominic_godfrey

  SSC-Addicted

  Points: 402

  More actions

  December 18, 2013 at 3:04 am

  #1674319

  MysteryJimbo (12/18/2013)

  ---

  Here is my problem, I have some stored procedures where the defintion from this query is not the same as when scripting the stored procedure in an alter statement.

  Does anyone know why the definition in SYS.OBJECTS might be wrong and how it can be solved?

  Thanks

  Dom

  It could be a problem caused by using sp_rename.

  https://www.simple-talk.com/blogs/2006/05/10/a-reason-to-avoid-sp_rename/[/url]

  You could use the scripting task from within the GUI. Right click the DB -> Tasks -> Generate Scripts..

  Alternatively you could use a third party DB comparison tool like Red Gate's SQL Compare.

  Thanks, your link provided a good explanation and I'm pretty sure that is what has been happening. I will make a note and alter the effected procedures to restore their definitions.

  Thanks again!

• Dominic_godfrey

  SSC-Addicted

  Points: 402

  More actions

  December 18, 2013 at 3:58 am

  #1674328

  Also, does anyone know what the stored procedure definitions are used for, I found that using sp_RefreshSQLModules uses the definition, but does nayone have any other examples or a comprehensive list, or a generic list?

  Thanks