Stored Procedure System Definition

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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